moving row data to a colunm in sorted order

[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

 

[Glenn]

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

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))

 

[Teethless mama]

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))

 

[Paul C]

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

 

[Max]

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

 

[moving row data to a column in a sorted]

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.

 

[moving row data to a column in a sorted]

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

 

[moving row data to a column in a sorted]

Looks interesting...I'm going to try this...thanks.

 

[moving row data to a column in a sorted]

thank you so much, Max

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]

welcome, ??