SOLVED

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

Copper Contributor

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

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

Butter

Milk

Butter

Butter

Banana

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

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

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

# Re: Formula look at range of cells in colum, find duplicates & return the the dup that appears t

Use MODE.SNGL or MODE.MULT.

# Re: Formula look at range of cells in colum, find duplicates & return the the dup that appears t

Hi Detlef

Is that available in Excel 2019?

# Re: Formula look at range of cells in colum, find duplicates & return the the dup that appears t

Opps do not worry it is.

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

# Re: Formula look at range of cells in colum, find duplicates & return the the dup that appears t

Check out the solution provided in attached worksheet.

1 best response

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

# Re: Formula look at range of cells in colum, find duplicates & return the the dup that appears t

Use MODE.SNGL or MODE.MULT.