nth most common value in an increasing range

duboost

New Member
Joined
Apr 8, 2010
Messages
41
Hi,

I have a list of names in the C column from C1:C50. In column A, i have formulas to display the nth most common name from column C.

A1=most common name
A2=2nd most common
A3=3rd most common
etc...

Here are the formulas I am using:

A1: =INDEX(C$1:C$50,MODE(MATCH(C$1:C$50,C$1:C$50,0)))
A2: {=INDEX(C$1:C$50,MODE(IF(COUNTIF(A$1:A1,C$1:C$50)=0,MATCH(C$1:C$50,C$1:C$50,0))))}
A3: {=INDEX(C$1:C$50,MODE(IF(COUNTIF(A$1:A2,C$1:C$50)=0,MATCH(C$1:C$50,C$1:C$50,0))))}

now this formula works fine when the range exactly matches the cells that have names in them, however the problem is that names are constantly being added to the range in column C. If i extend the range in the formula to a number far beyond the filled cells, C1:C500 for example, the formulas will return #N/A. Is there a way to make it so i don't have to adjust the range every time a new name is added? Thank you
 
Last edited:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Can you please provide how this worked for you? I'm trying to use dynamic Named range and it doesn't seem to work for me. Can you show your formula that you put into the dynamic name manager? PLEASE HELP!!!
 
Upvote 0
FIGURED IT OUT!! WOO HOO!!

Instead of giving your range like C1:C50, insert your dynamic name range (mine was called Test) so the formula should look like this:

A1: =INDEX(Test,MODE(MATCH(Test,Test,0)))
A2: {=INDEX(Test,MODE(IF(COUNTIF(A$1:A1,Test)=0,MATCH(Test,Test,0))))}
A3: {=INDEX(Test,MODE(IF(COUNTIF(A$1:A2,Test)=0,MATCH(Test,Test,0))))}
A4: {=INDEX(Test,MODE(IF(COUNTIF(A$1:A3,Test)=0,MATCH(Test,Test,0))))}

Make sure you click Use in Formula when you put your range in and not just type it.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

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