Formula to determine rank, given set of values

kwp004

Board Regular
Joined
Dec 27, 2016
Messages
93
In column A, I have a set of unique values, and in column B, I have another set of unique values. I'm trying to develop a formula that would tell me the rank of the most common value in column B, given the value in column A.

So for example, for AA, it'd be #1 Dog, #2 Cat, #3 Bird.

AACat
AACat
AADog
AADog
AADog
AADog
AABird
BBCat
BBCat
BBBird

I've been trying to use a combination of countifs and mode, without much success.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Do a search for ranking with sumproduct this should help you
 
Upvote 0
The problem with that approach is that I don't have numbers in my dataset, it's all non-numeric values (similar to the example I provided).
 
Upvote 0
What is your expected result?power query may solve this very easily.
 
Upvote 0
Maybe you need something like this? This is done by Power Query and I need the exact layout of your expected result to modify the code.

1601638375110.png



if you only need the sequence of the items, Pivot Table will do:

1601638580659.png
 
Upvote 0
This can be done using formula also
However if the count of Two criteria are same then it will consider the criteria selected to be Rank greater than the others

Book1
ABCDEFGH
1NameHelper
2AACat0Rank
3AACat0BBBird1
4AADog0
5AADog0
6BBCat3
7BBBird3
8BBCat0
9BBCat0
10BBBird0
11BBBird0
12
Sheet2
Cell Formulas
RangeFormula
H3H3=RANK.EQ(COUNTIFS(A2:A11,F3,B2:B11,G3),C2:C11,0)
C2:C11C2=IF(COUNTIFS($A$1:A2,$F$3,$B$1:$B2,B2)=1,SUM(($A$2:$A$11=$F$3)*($B$2:$B$11=B2)*IF($A$2:$A$11=$F$3,(ROW($A$2:$A$11)-ROW($A$2)+1)^0)),0)
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,438
Members
448,573
Latest member
BEDE

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