Ranking Data in Alphabetical Order

stc60

New Member
Joined
Dec 3, 2018
Messages
10
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.
 
Upvote 0
This is an array formula, please enter with Ctrl+Shift+Enter
 
Upvote 0
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))
 
Upvote 0
=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.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top