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.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Do a search for ranking with sumproduct this should help you
 

kwp004

Board Regular
Joined
Dec 27, 2016
Messages
93
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).
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows
What is your expected result?power query may solve this very easily.
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
893
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,808
Messages
5,627,010
Members
416,214
Latest member
boston814

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
Top