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