moving row data to a colunm in sorted order

  • Thread starter Thread starter moving row data to a column in a sorted
  • Start date Start date
M

moving row data to a column in a sorted

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
 
moving said:
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
Click to expand...


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))
 
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))
 
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
 
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
 
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.
 
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
 
thank you so much, Max

Max said:
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
Click to expand...
 
Back
Top