Ranking Data in Alphabetical Order

[stc60]

I would appreciate some help with this task as I am really stuck trying to work this out. I will try my best to explain it. I have 2 worksheets in the excel file. The first worksheet is where all the data is sent from the 3rd party software. I have no control over how the data is displayed on the first worksheet. All the calculations are completed on the 2nd worksheet. Below is what I am trying to complete on the 2nd worksheet. The D,Z,A,P,W text is what is shown on B9,B10,B11,B12,B13 of sheet1. The problem is the data is continually changing positions. The data may change to Z,D,P,A,W 10 minutes after this example. What I want to do is on another section of the 2nd worksheet is create Alphabetical ranking. So the data is always shown as A,D,P,W,Z. Any help appreciated

='Sheet1'!B9 D
='Sheet1!B10 Z
='Sheet1!B11 A
='Sheet1!B12 P
='Sheet1!B13 W

 

[Phuoc]

Try this:

=INDEX(Sheet1!$B$9:$B$13,MATCH(SMALL(COUNTIF(Sheet1!$B$9:$B$13,"<"&Sheet1!$B$9:$B$13),ROWS($A$1:A1)),
COUNTIF(Sheet1!$B$9:$B$13,"<"&Sheet1!$B$9:$B$13),0))

Enter with Ctrl+Shift+Enter and copy down.

 

[stc60]

Phuoc, Thanks for the help. I am getting #NUM ! in the cells on worksheets 2.

 

[Phuoc]

This is an array formula, please enter with Ctrl+Shift+Enter

 

[stc60]

Try this:

=INDEX(Sheet1!$B$9:$B$13,MATCH(SMALL(COUNTIF(Sheet1!$B$9:$B$13,"<"&Sheet1!$B$9:$B$13),ROWS($A$1:A1)),
COUNTIF(Sheet1!$B$9:$B$13,"<"&Sheet1!$B$9:$B$13),0))

Enter with Ctrl+Shift+Enter and copy down.

Thanks Phuoc, I got it working. I have changed the code slightly to reference the same worksheet as I have decided to adapt the spreadsheet further. Its still working but the problem now is the first section of text is repeated 3 times, so if I use the example above I get A,A,A,D,P,W,Z when I only want to see A,D,P,W,Z


=INDEX($B$6:$B$20,MATCH(SMALL(COUNTIF($B$6:$B$20,"<"&$B$6:$B$20),ROWS($A$1:A1)),COUNTIF($B$6:$B$20,"<"&$B$6:$B$20),0))

 

[stc60]

=INDEX($B$9:$B$20,MATCH(SMALL(COUNTIF($B$9:$B$20,"<"&$B$9:$B$20),ROWS($A$1:A1)),COUNTIF($B$9:$B$20,"<"&$B$9:$B$20),0))

I have worked out the problem. If I use the above code and there is only text in cells B9 to say B15, it will duplicate the first text 5 times so with the above example it would be A,A,A,A,A,A,D,P,W,Z. So depending on how many blank cells there are in the B Column between B9 and B20 determines how many duplicates of the first text there is. Can anyone help me with changing the code so this doesn't happen.

 

[Phuoc]

Try this formula in C2:

=IFERROR(INDEX($B$9:$B$20,
MATCH(AGGREGATE(15,6,COUNTIF($B$9:$B$20,"<"&$B$9:$B$20)/(COUNTIF($C$1:C1,$B$9:$B$20)=0)/($B$9:$B$20<>""),1),
COUNTIF($B$9:$B$20,"<"&$B$9:$B$20)/(COUNTIF($C$1:C1,$B$9:$B$20)=0)/($B$9:$B$20<>""),0)),"")


Enter with Ctrl+Shift+Enter and copy down.

 

[stc60]

Thanks Phuoc