Formula look at range of cells in colum, find duplicates & return the the dup that appears the most.

Copper Contributor

Windows 10

Excell 2029


I need a formula that looks at cells in a range, finds any duplicate and returns the duplicate that appears the most.

An example would be

Column A








and the formula result would be Butter in Cell C2

I already have the code that looks at Column A and I have marked where the 2nd formula would be.

Either version of the formula is entered into C2 and looks at cell A2.
If A2 is NOT null then the formula that I am looking for would run
and return the result in C2


A1 Format



'=IF(A2="","","Code Goes Here")




R1C1 Format



ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""","""",""Code Goes Here"")"




Thank you in advance 

4 Replies
best response confirmed by Kenneth Green (Copper Contributor)
Hi Detlef

Is that available in Excel 2019?
Opps do not worry it is.

Thank you for your suggestion and I will go and try it out

@Kenneth Green 

Check out the solution provided in attached worksheet.



1 best response

Accepted Solutions
best response confirmed by Kenneth Green (Copper Contributor)