Results 1 to 7 of 7
Thread: Sorting (2000)

20030813, 17:23 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Sorting (2000)
Problem: In column A, Rows 15, I have the names Alice, Bill, Charles, Debbie, and Edward. In column B, Rows 15, are the figures $100, $150, $135, $115, and $160. What I would like to do is the following: (1) sort Column A cells A1A5, beginning at Row 7 in that column, based on the values in Column B, Rows 15, from lowest to highest (for example, the entry in cell A7 would be Alice, and the contents in cell B7, $100.
Any ideas?

20030813, 18:13 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sorting (2000)
I am confused. Could you explain more clearly what you want to do?
How can you sort Cells A1:A5 beginning in row 7? The range has no row 7.
Steve

20030813, 18:19 #3
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Sorting (2000)
Sorry about that! What I would like to do is enter a formula in cells A7A11 the would enter the "results" of a sort of cells A1A5 that would be performed based on the ascending values in cells B1 through B5. In addition, cells B7B11 would contain the respective $ amounts corresponding to the names in cells A7A11 (i. e., the sorted names).
I hope this is clearer.

20030814, 00:36 #4
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Sorting (2000)
Steve,
Works finethanks a bunch! Would you do me the honor of explaining your first formula (the Index formula)what it does, etc.?
Thanks again.
Jeff

20030814, 09:59 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sorting (2000)
If I understand (and I am NOT sure that I do)
put the formula in A7:
<pre>=INDEX($A$1:$A$5,MATCH(SMALL($B$1:$B$5,ROW( )ROW($A$7)+1),$B$1:$B$5,0))</pre>
and in B7:
<pre>=VLOOKUP(A7,$A$1:$B$5,2,FALSE)</pre>
Copy A7:B7 to A8:B11
A7:A11 are the "sorted" names based on ascending B1:B5
B7:B11 looks up the value for the appropriate name in A7:A11
Steve

20030814, 10:10 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sorting (2000)
<pre>=INDEX($A$1:$A$5,MATCH(SMALL($B$1:$B$5,ROW( )ROW($A$7)+1),$B$1:$B$5,0))</pre>
<pre>ROW()</pre>
gives the row number of the cell
<pre>ROW($A$7)</pre>
gives the row of the TOP cell in your bottom range (in this case it gives 7) but if you insert rows after A6 this will be adjusted aas A7 moves. It is "locked" on A7 so if it is copied it stays as A7.
thus:
<pre>ROW()ROW($A$7)+1</pre>
just gives numbers from 1 (if cell is A7) to 5 (if cell with formula is A11)
<pre>SMALL($B$1:$B$5,ROW()ROW($A$7)+1)</pre>
Small ($B$1:$B$5,1) gives the min value (smallest "1") in B1:B5
Small ($B$1:$B$5,2) gives the 2nd smallest (smallest "2") in B1:B5, etc
So this essentially "sorts" the numbers in B1B5 giving in A7 the smallest 1, A8 the smallest2, etc
<pre>MATCH(SMALL($B$1:$B$5,ROW()ROW($A$7)+1),$B$1:$B$5,0)</pre>
"looks up" the "small value" from that row in B1:B5 (with exact match) and gets the "row index"
<pre>=INDEX($A$1:$A$5,MATCH(SMALL($B$1:$B$5,ROW( )ROW($A$7)+1),$B$1:$B$5,0))</pre>
Looks up the "name" in A1:A5 that has the given "row index"
Steve

20030814, 12:31 #7
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Sorting (2000)
Thanks, Steve.