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

1. ## 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. ## 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),"")

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

#### Posting Permissions

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