| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving row data to a colunm in sorted order
I have spreadsheet of several columns and rows. I have taken the row of
totals and moved them to a new column in a sorted order using (=SMALL) statistical function. The problem is I don't know how to obtain the row headings and match them up to the new column of ordered numbers. As an example below, does anyone know how to match the row names to the new column of numbers. john pat bob new column 13 22 14 16 01 10 14 33 02 12 05 44 ------------------ 16 44 33 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
moving row data to a colunm in sorted order
moving row data to a column in a sorted wrote:
I have spreadsheet of several columns and rows. I have taken the row of totals and moved them to a new column in a sorted order using (=SMALL) statistical function. The problem is I don't know how to obtain the row headings and match them up to the new column of ordered numbers. As an example below, does anyone know how to match the row names to the new column of numbers. john pat bob new column 13 22 14 16 01 10 14 33 02 12 05 44 ------------------ 16 44 33 Assuming the names are in A1:C1, the original totals are in A5:C5 and "new column" totals are in G2:G4, the following formula should work for you: =INDEX($A$1:$C$1,MATCH(G2,$A$5:$C$5,0)) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
moving row data to a colunm in sorted order
Assuming your data in A1:C5
your result in new column is E2:E5 in F2: =INDEX($A$1:$C$1,MATCH(E2,$A$5:$C$5,0)) "moving row data to a column in a sorted" wrote: I have spreadsheet of several columns and rows. I have taken the row of totals and moved them to a new column in a sorted order using (=SMALL) statistical function. The problem is I don't know how to obtain the row headings and match them up to the new column of ordered numbers. As an example below, does anyone know how to match the row names to the new column of numbers. john pat bob new column 13 22 14 16 01 10 14 33 02 12 05 44 ------------------ 16 44 33 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
moving row data to a colunm in sorted order
I was going along the same lines and know I am over thinking it, but thought
of this problem. What if two of the totals are the same? The match only finds the first one. I started with a nested Match/Offset to redefine the range. It got long really fast and only works if there are only two of the same and not three. INDEX(OFFSET(A1,0,MATCH(E4,$A$5:$C$5,0),1,COLUMNS( A5:C5)-MATCH(E4,$A$5:$C$5,0)),MATCH(E4,OFFSET(A5,0,MATCH( E4,$A$5:$C$5,0),1,COLUMNS(A5:C5)-MATCH(E4,$A$5:$C$5,0)),0)) There has got to be an easier way -- If this helps, please remember to click yes. "Teethless mama" wrote: Assuming your data in A1:C5 your result in new column is E2:E5 in F2: =INDEX($A$1:$C$1,MATCH(E2,$A$5:$C$5,0)) "moving row data to a column in a sorted" wrote: I have spreadsheet of several columns and rows. I have taken the row of totals and moved them to a new column in a sorted order using (=SMALL) statistical function. The problem is I don't know how to obtain the row headings and match them up to the new column of ordered numbers. As an example below, does anyone know how to match the row names to the new column of numbers. john pat bob new column 13 22 14 16 01 10 14 33 02 12 05 44 ------------------ 16 44 33 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
moving row data to a colunm in sorted order
What if two of the totals are the same? The match only finds the first one
One way to auto-transpose & tiebreak it for the OP ... Assume source names in A1:C1, totals in A5:C5 In E2: =IF(INDEX($A$5:$C$5,ROWS($1:1))="","",INDEX($A$5:$ C$5,ROWS($1:1))+ROW()/10^10) In F2: =INDEX($A$1:$C$1,MATCH(SMALL($E$2:$E$4,ROWS($1:1)) ,$E$2:$E$4,0)) In G2: =INDEX($A$5:$C$5,MATCH(SMALL($E$2:$E$4,ROWS($1:1)) ,$E$2:$E$4,0)) E2:G2 is copied down to G4. F2:G4 will return the names-totals autosorted in ascending order by totals. Tied names, if any, will be returned in the same relative order that they appear within the source. voila? hit YES -- Max Singapore --- |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
moving row data to a colunm in sorted order
yes there is a problem if the totals a re the same, I've been toying with this
problem for a while. I just can't find a solution. Thanks for your time. "Paul C" wrote: I was going along the same lines and know I am over thinking it, but thought of this problem. What if two of the totals are the same? The match only finds the first one. I started with a nested Match/Offset to redefine the range. It got long really fast and only works if there are only two of the same and not three. INDEX(OFFSET(A1,0,MATCH(E4,$A$5:$C$5,0),1,COLUMNS( A5:C5)-MATCH(E4,$A$5:$C$5,0)),MATCH(E4,OFFSET(A5,0,MATCH( E4,$A$5:$C$5,0),1,COLUMNS(A5:C5)-MATCH(E4,$A$5:$C$5,0)),0)) There has got to be an easier way -- If this helps, please remember to click yes. "Teethless mama" wrote: Assuming your data in A1:C5 your result in new column is E2:E5 in F2: =INDEX($A$1:$C$1,MATCH(E2,$A$5:$C$5,0)) "moving row data to a column in a sorted" wrote: I have spreadsheet of several columns and rows. I have taken the row of totals and moved them to a new column in a sorted order using (=SMALL) statistical function. The problem is I don't know how to obtain the row headings and match them up to the new column of ordered numbers. As an example below, does anyone know how to match the row names to the new column of numbers. john pat bob new column 13 22 14 16 01 10 14 33 02 12 05 44 ------------------ 16 44 33 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
moving row data to a colunm in sorted order
yes, that works but what doesn't work and has had me puzzled for some time is
how to match the total in the new column with the person "Glenn" wrote: moving row data to a column in a sorted wrote: I have spreadsheet of several columns and rows. I have taken the row of totals and moved them to a new column in a sorted order using (=SMALL) statistical function. The problem is I don't know how to obtain the row headings and match them up to the new column of ordered numbers. As an example below, does anyone know how to match the row names to the new column of numbers. john pat bob new column 13 22 14 16 01 10 14 33 02 12 05 44 ------------------ 16 44 33 Assuming the names are in A1:C1, the original totals are in A5:C5 and "new column" totals are in G2:G4, the following formula should work for you: =INDEX($A$1:$C$1,MATCH(G2,$A$5:$C$5,0)) . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
moving row data to a colunm in sorted order
Looks interesting...I'm going to try this...thanks.
"Max" wrote: What if two of the totals are the same? The match only finds the first one One way to auto-transpose & tiebreak it for the OP ... Assume source names in A1:C1, totals in A5:C5 In E2: =IF(INDEX($A$5:$C$5,ROWS($1:1))="","",INDEX($A$5:$ C$5,ROWS($1:1))+ROW()/10^10) In F2: =INDEX($A$1:$C$1,MATCH(SMALL($E$2:$E$4,ROWS($1:1)) ,$E$2:$E$4,0)) In G2: =INDEX($A$5:$C$5,MATCH(SMALL($E$2:$E$4,ROWS($1:1)) ,$E$2:$E$4,0)) E2:G2 is copied down to G4. F2:G4 will return the names-totals autosorted in ascending order by totals. Tied names, if any, will be returned in the same relative order that they appear within the source. voila? hit YES -- Max Singapore --- |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
moving row data to a colunm in sorted order
thank you so much, Max
"Max" wrote: What if two of the totals are the same? The match only finds the first one One way to auto-transpose & tiebreak it for the OP ... Assume source names in A1:C1, totals in A5:C5 In E2: =IF(INDEX($A$5:$C$5,ROWS($1:1))="","",INDEX($A$5:$ C$5,ROWS($1:1))+ROW()/10^10) In F2: =INDEX($A$1:$C$1,MATCH(SMALL($E$2:$E$4,ROWS($1:1)) ,$E$2:$E$4,0)) In G2: =INDEX($A$5:$C$5,MATCH(SMALL($E$2:$E$4,ROWS($1:1)) ,$E$2:$E$4,0)) E2:G2 is copied down to G4. F2:G4 will return the names-totals autosorted in ascending order by totals. Tied names, if any, will be returned in the same relative order that they appear within the source. voila? hit YES -- Max Singapore --- |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
moving row data to a colunm in sorted order
welcome, ??
-- Max Singapore "moving row data to a column in a sorted" soft.com wrote in message ... thank you so much, Max |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hyperlinks not moving with cells when data is sorted | Excel Worksheet Functions | |||
| Can three separate sections of rows be sorted in the same order? | Excel Worksheet Functions | |||
| Comments not moving when columns are sorted | Excel Discussion (Misc queries) | |||
| series order - sorted? | Charts and Charting in Excel | |||
| Why must the table for Vlookup be sorted in ascending order? | New Users to Excel | |||
Linear Mode
