nth most common value in an increasing range

[duboost]

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

 

[shg]

Dynamic named ranges: http://www.contextures.com/xlnames01.html

 

[duboost]

Dynamic named ranges: http://www.contextures.com/xlnames01.html

Perfect, thank you

 

[kiyo052]

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!!!

 

[kiyo052]

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.