1. ## Sorting (2000)

Problem: In column A, Rows 1-5, I have the names Alice, Bill, Charles, Debbie, and Edward. In column B, Rows 1-5, are the figures \$100, \$150, \$135, \$115, and \$160. What I would like to do is the following: (1) sort Column A cells A1-A5, beginning at Row 7 in that column, based on the values in Column B, Rows 1-5, from lowest to highest (for example, the entry in cell A7 would be Alice, and the contents in cell B7, \$100.

Any ideas?

2. ## 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

3. ## Re: Sorting (2000)

Sorry about that! What I would like to do is enter a formula in cells A7-A11 the would enter the "results" of a sort of cells A1-A5 that would be performed based on the ascending values in cells B1 through B5. In addition, cells B7-B11 would contain the respective \$ amounts corresponding to the names in cells A7-A11 (i. e., the sorted names).

I hope this is clearer.

4. ## Re: Sorting (2000)

Steve,
Works fine-thanks a bunch! Would you do me the honor of explaining your first formula (the Index formula)-what it does, etc.?
Thanks again.
Jeff

5. ## 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

6. ## 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 B1-B5 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

7. ## Re: Sorting (2000)

Thanks, Steve.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•