Using Large Function To Sort Column Of Numbers
Dec 17, 2012
I have 3 columns of data: col. A = name (random order), col. B = Net #, Col. C = Gross #.
I am using =LARGE(C$1:C$4466,ROWS($D$1:D1) to Automatically sort col C in decending order.
I would Like to do another decending sort but only the values in Col C that corespond to a particular name in Col A. Can I imbed a index match function combination within the large function to do this?
Name
Net
Gross
All Sort
SortA
SortB
SortC
SortD
A
508
-200.129
101.685
[Code] ..........
View 6 Replies
ADVERTISEMENT
May 7, 2013
I need to create a custom function that can do the following without modifying the selected range. If I have data in a row, I need a function that can give me the following result:
Original unsorted data:
79
45
23
56
1
The custom formula will not modify the selected range, and after saving it or copying somewhere and sorting it, it will provide the following result: if N or count(range)=5 , and p=1, average(second and third smallest values of the range)=34; and if N=4 (lets say that we errase the number 1 data point in the range), average(third and fourth smallest values of the range)=67.5 So it is basically a sort and then getting the averages of specified row numbers depending on N or count(range) Here is what I have.
I'm missing how to create a variable with the sorted range and how to use an index to refer to the rows I want to average:
Function CSORT(r, p)
Application.Volatile
'rs or something similar that will contain the sorted range r
Dim rs As Range
'Sort rs (The following line one doesn't work, so I need one that does)
rs = Application.Range(r).Sort
Dim p As Integer
[code]....
View 1 Replies
View Related
Nov 27, 2012
I am trying to insert three columns within a large amount of data. I am using Excel 2003 edition. The three columns need to measure max, min, and standard deviation of month long ranges and the data goes all the way back to 1993.
Currently, I have a column that has the correct ranges but finds the average for each month
(=AVERAGE($H7214:$H7243))...
And many more ranges as it dates back all the way to '93. Is there a possible way to insert these three columns with their respective commands (=MAX... =MIN... etc.) while keeping all the ranges from the AVERAGE column.
In effect, I am looking to solely switch the begining of the column command
(=AVERAGE($H7214:$H7243)) to (=MIN($H7214:$H7243) etc...
While keeping all of the specified ranges from the AVERAGE column.
View 2 Replies
View Related
Oct 5, 2007
I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending
ex) 1.751
2.52
3.753
418
417
416
View 5 Replies
View Related
Jan 23, 2009
I have data in column C of rows 25 to 45. The data varies in number form.
What i would like to do is have some VBA code that i can place into a macro which will sort the numbers in column C of rows 25 to 45 putting the highest number at the the top (C25). But i need to move the complete row.
View 9 Replies
View Related
Nov 29, 2012
I have a list of files in folders.
When I make the index of those folder, i have an excel list with in column A these numbers
1.1.1.1 (so the file 1 in the 1st subsubfolder in the 1st subfolder in the first folder)
1.1.1
1.2.1.1
1.2.2
1.3.1
etc... The list goes on with 1000s rows.
And in column B I have the names of the files
Unfortunately, when i sort the spreadsheet with the number on column A, the order is not good....
So I would like to find a solution to sort things in the good order.
View 7 Replies
View Related
Feb 14, 2003
I have three columns of values that are in no particular order that need to be sorted in ascending order in another column.
View 9 Replies
View Related
Aug 12, 2007
I am attempting to sort a column with mixed letters and numbers. However, I have been totally unable to get them to sort the way I want them.
What I have is:
K600
K2
K2A
K2B
K159
K159A
K159B
K1
K1A
K1B
K428
K8
K8A
K8B
etc, etc. The prefix letter is only a K - no other letters. However, the suffix letters can be anything from A to E (at the present time)
I need to sort them numberically from K1 in descending order ie:
K1
K1A
K1B
K2
K3
K4
K4A
K4B
etc etc etc
View 9 Replies
View Related
Jun 8, 2014
I would like to create a formula that will sort the numbers in column B, there are also equal numbers in this column
The difficult part is that there is text in column A, and is linked to the numbers
So if numbers are sorted the names should be sorted in the same way as well.
View 3 Replies
View Related
Jan 7, 2010
Have a spreadsheet that contains a column of 3 digit numbers as well as 3 digit numbers with 2 trailing alpha characters.
Example:
376
377
421
376AB
376XY
377NC
421GQ
421EF
Need to sort by this column, but, with the parameter of sorting first by the numeric only, and then by numeric with alphas. So, the above list would look like this sorted properly:
376
376AB
376XY
377
377NC
421
421EF
421GQ
View 9 Replies
View Related
Jul 19, 2014
I have a text file with rows and columns of numbers ranging from 1-4 digits that I'd like to import/copy into Excel with each number being in its own cell. But whenever I copy/try to import, Excel splits all of the 3-4 digit numbers up into single digit numbers. The text file has 10,000+ columns (each number occupies two columns so I have half of that amount in numbers) and 300+ lines.
Is the file simply too large for Excel to handle or is there a way I can do this?
View 14 Replies
View Related
Oct 11, 2013
I have a list of domains (about 200,000) that looks like this:
I have a list of urls (about 1 million) that looks like this:
I want my results to only show this (since this is the only thing in the second file that does not contain anything from the first file):
I have tried programs designed to do this but they either (a) shorten everything down to the domain or (b) wont remove anything since the domains are part of a longer url in the second file.
View 3 Replies
View Related
Aug 27, 2012
I have a column (B) of randomly generated numbers 1-14, and am using this formula range to sort in descending order and return the relevant value from column (c).
=VLOOKUP(LARGE(B3:B20,1),B3:C20,2,) to =VLOOKUP(LARGE(B3:B20,14),B3:C20,2,)
Works great, except when a number is duplicated, (E.G. 14,13,13,12,11,10,10,9,8 etc). It then returns the first value from(C) repeated, and not the value from the second and subsequent duplicated reference numbers.
View 3 Replies
View Related
Feb 9, 2012
I have a question about sorting. I would like to sort multiple columns from small to large at the same time. Is this possible? I have over 400 columns to sort so I would rather not do this one column at the time.
Example
A.... B.... C
0.2 0.4 0.3
0.4 0.3 0.5
0.1 0.6 0.1
shoud be
A.... B.... C
0.1 0.3 0.1
0.2 0.4 0.3
0.4 0.6 0.5
View 1 Replies
View Related
Jan 25, 2009
I don't think there is such a function but is there a way that I can have a function that constantly adds the numbers in a column and when the sum of 240 is reached it adds 30. I need it to do this only once and not keep adding at every 240 point.
View 2 Replies
View Related
Mar 25, 2009
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.
My
Public Function SortRange(rngToSort As Range, valCol As Integer)
Dim Swapper As Variant
Dim i As Integer, _
j As Integer, _
k As Integer
For i = 1 To rngToSort.Rows.Count
For j = 1 To rngToSort.Rows.Count - i
If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then
For k = 1 To rngToSort.Columns.Count
Swapper = rngToSort(j, k)
rngToSort(j, k) = rngToSort(j + 1, k)
rngToSort(j + 1, k) = Swapper
Next k
End If
Next j
Next i
SortRange = rngToSort
End Function
View 9 Replies
View Related
Dec 1, 2007
I need to create a function that numbers rows with respect to data groups in a column (column labeled "Type" in this example). The result would be that shown in column A in this table.
How do I write the function?
#TypeName Date
1CarsJohn1/12/2008
2CarsJane11/10/2007
3CarsMary11/2/2004
4CarsBob12/7/2003
1TrucksMike12/12/2007
2TrucksSandy1/3/2007
3TrucksDale12/14/2006
4TrucksVince4/8/2005
*
View 10 Replies
View Related
Jan 21, 2012
Using the following data
R10-12128
R11-12x12x8
R11-12x12x8
R1-12x12x8
R1-12x12x8
R12-12x12x8
R14-12x12x8
R16-12x12x8
R18-12x12x8
R2-12x12x8
I want it to sort like this:
R1-12x12x8
R1-12x12x8
R2-12x12x8
R10-12128
R11-12x12x8
R11-12x12x8
R12-12x12x8
R14-12x12x8
R16-12x12x8
R18-12x12x8
What is the formula to achieve this?
View 5 Replies
View Related
Jul 14, 2009
I'm trying to create a mini-table that will give me the 3 highest and lowest values in a range (I know how to do this using LARGE and SMALL functions), but I also want to get the corresponding name (in column A) for each number. This last part I don't know how to do.
I've attached an example sheet that I hope gives a basic idea of what I'm trying to do. I'm looking for a method to fill in the data for cells A13:A15 and C13:C15 in the attached sheet.
I could modify my worksheet a bit and probably get the result I'm looking for, but I'm hoping to come up with a nicer solution, and hopefully learn Excel a bit better in the process.
View 2 Replies
View Related
Dec 28, 2013
How can you sort item numbers, targeting only the numbers in the mock up file?
Example:
item number: GW14SO0000003
item company: GW
item location: 14SO
item counter: 0000003
How can I only sort the item by the last 7 digits "0000003"
View 1 Replies
View Related
Nov 13, 2013
I am looking for a VBA to sort rows which include actual numbers and text representing decomposed CTQs (or procedures in IT development)
Code:
Col A Col B
1Billing Accuracy
2Billing Time
3Credit Check Accuracy
4Credit Check Time
2.1Bill preparation
[Code]....
This is the order in which the data is copied and saved from worksheets in which they are developed. Note that 3 rows (8.1.2.1 through 8.1.2.3) are below 8.1.3 (because the three come from Worksheet 8.1.2 which came after worksheet 8.1). The first four rows came from a Top Level Worksheet. I would like to see them intermixed but in proper order.
View 9 Replies
View Related
Aug 24, 2014
I was doing this task using sorting then adding column today morning one of my friend told i can do it with formula only, no need to using sort and adding new column
I just attached the sheet : without sorting.xlsx
View 5 Replies
View Related
Jun 8, 2007
Column C contains the names of real-estate brokers.
Column E contains the colors of the homes for sale.
Column Q contains the values of the homes.
I want to return the sum of the 50 most expensive homes that are yellow and for sale by Century 21.
I'm familiar with array formulas, and with the large function. I just do not know how to incorporate the large function within the sum/array formula.
View 9 Replies
View Related
Oct 20, 2009
I have a SS that has 20,000 rows. One of the columns contains numbers. I'm tryiny to filter out the numbers that are NOT 7 digits long. The problem i have is that some of the cells have space in between the numbers. e.g 240 1332, 432 2443, 234 2345 234 etc. And some are normal e.g 4234535, 53596835, 3459284, 2423 etc. So, filtering between ranges isn't working i think because numbers with spaces in them aren't read as numbers in excel.
View 4 Replies
View Related
May 8, 2014
I've got some data which will look something like the following:
987249879238Steven1987dob98023498092384029834Tom1972dob298374928374928374987
I'm looking to remove any set of numbers more than 10 characters long. i.e. the desired output from the above would be:
Steven1987dobTom1972dob
View 4 Replies
View Related
Jan 13, 2014
Using DataEntry sheet for data.
Trying to rearrange the data to DataFormatedProperly sheet.
So far all I can accomplish is DataFormatedWrong sheet.
Edit: Not sure what happened but file was NOT understandable before. It should be correct now.
View 2 Replies
View Related
Feb 1, 2010
I am trying to use the large function over two ranges. Even though incorrect I will include my original formula that doesn't work to help illustrate.
if(large((L20:L500,W20:W500),1)=I20,$P$4,if((large((L20:L500,W20:W500),2)=I20,$P$5,0)))
Does anybody know of an easy way around this?
View 6 Replies
View Related
Mar 7, 2006
Excel vlookup function returns #VALUE when the lookup value exceeds 256
characters. I need a hash function to transform large strings into a value
that does not exceed the 256 character limit.
View 14 Replies
View Related
Apr 5, 2012
I use a large function when ranking numbers in an array each month. I'm only interested in the top 5 numbers. However, there are occasions when the top 5 numbers contain a tie. How do you build into a large function, logic to handle a tie. Here's my function, which is very simple:
Code:
=LARGE(C$6:C$40,1)
=LARGE(C$6:C$40,2)
ETC.
View 1 Replies
View Related
May 23, 2012
I know how to use the large function when looking for a specific criteria as the example below illustrates:
Code:
=IFERROR(LARGE(IF('2012'!$D$1:$D$7="Green",'2012'!$C$1:$C$7),1),"-")
What I'm trying to do is take this one step further and not only find the nth largest numbers in a set of data based on a particular criteria, but also sum those numbers because they repeat in a table: for example a sample table is below:
Account Accout # Store # channel $ sales
A 1000 10001 green $100
B 1001 10011 green $230
A 1000 10002 green $120
C 1002 10021 brown $145
A 1000 10003 green $100
D 1003 10031 red $20
B 1001 10012 green $50
So what I'm basically attempting to do is bring in the nth largest accounts within the "green" channel. Now if these were the top 5 stores I was looking for, the formula from above would suffice. However since this deals with accounts and the account # repeat I need to bring in the total sum of those repeating accounts instead of just one of the unique stores. So if I was seeking the largest account (NOT store) within the "green" channel the correct values this formula would be:
Account A $320
I would imagine we would need to combine a sumif with a large function or maybe involve sumproduct somehow.
View 6 Replies
View Related
