Need Excel Macro or Formula for finding duplicated cells more than X times

[bnmtl]

Need Excel Macro or Formula for finding duplicated cells more than 5 times and copy them into new sheet.
Can you please help me with that ?

For Example;


apple
orange
apple
banana
apricot
apple
orange
apricot
fruit
lemon
lime
plum
apple
banana
orange
apple
orange
orange

OUTPUT in new Sheet will be

apple 5
orange 5

[humdingaling]

assuming range is A1:a18

Please Login or Register  to view this content.

or

if you want the whole column

Please Login or Register  to view this content.

[bnmtl]

But what if I have 10000 records? and I want to calculate names automatically, its going to seperate automatically.
=COUNTIF(A:A,"orange") find only orange, what about others ?

[humdingaling]

either make your range to be 10000 or do the whole column
the other fruit you will need to either refer to list of fruits in cells or type out the names themselves

[humdingaling]

if you want more advanced naming
you will need some sort of CSE formula to create a unique list
see attached example

[bnmtl]

Seems perfect, addition to this, cant we make list only repeated items more than 5 times ? This is counting 1 by 1

[humdingaling]

cannot see how to achieve this without a much more complex formula or helper column

i am unable to figure out more complex formula for counts > 5

solution with helper column attached

[kalak]

try

Please Login or Register  to view this content.