Results 1 to 7 of 7

Thread: Sorting (2000)

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Bronze Lounger
    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 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. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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 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. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
  •