Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re-arranging List According to Ranking (Excel 2000)

    What is the easiest way to re-arrange an array of data according to order of a particular column without using macro. For example, Column A1:A5 has John, Smith, Joe, Jack, Susan. B1:B5 has 4,3,5,2,1. Is there an easy formula to re-arrange them in C15 accoring to the order of B1:B5 ? so that C1:C5 reads: Susan, Jack, Smith, John and Joe. D15 reads: 1,2,3,4,5. I've used Rank() function plus vlookup(), but it's kind of awkward and requires additional columns.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Re-arranging List According to Ranking (Excel 2000)

    In C1 enter & copy down:

    =IF(ROW()-ROW($C$1)+1<=COUNT($B$1:$B$5),INDEX($A$1:$A$5,MATC H(ROW()-ROW($C$1)+1,$B$1:$B$5,0)),"")

    or just:

    =INDEX($A$1:$A$5,MATCH(ROW()-ROW($C$1)+1,$B$1:$B$5,0))

    In D1 enter & copy down:

    =IF(C1<>"",COUNTA($C$1:C1),"")
    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Re-arranging List According to Ranking (Excel 2000)

    Thanks. However, it seems that using the suggest formula, B1:B5 must be 5 consecutive numbers starting from 1 (even though can be in different orders). If B1:B5 are 9,5,6,4,2, the formula does not work.

  4. #4
    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: Re-arranging List According to Ranking (Excel 2000)

    How about these (change ranges as appropriate):

    In C1and copy down)
    <pre>=INDEX($A$1:$A$5,MATCH(D1,$B$1:$B$5,0))</pre>


    In D1 and copy down:
    <pre>=SMALL($B$1:$B$5,ROW())</pre>


    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Re-arranging List According to Ranking (Excel 2000)

    OK. It looks like you need ranking first...

    In C1 enter & copy down:

    =RANK(B1,$B$1:$B$5)+COUNTIF($B$1:B1,B1)-1

    In D1 enter & copy down:

    =IF(ROW()-ROW($D$1)+1<=COUNT($B$1:$B$5),INDEX($A$1:$A$5,MATC H(ROW()-ROW($D$1)+1,$C$1:$C$5,0)),"")

    In E1 enter & copy down:

    =IF(ROW()-ROW($D$1)+1<=COUNT($B$1:$B$5),INDEX($B$1:$B$5,MATC H(ROW()-ROW($D$1)+1,$C$1:$C$5,0)),"")

    The re-arrangement takes place in D:E instead of C.
    Microsoft MVP - Excel

Posting Permissions

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