Counting duplicates

[madd]

There is likely a very simple solution to this but I haven't been able to come up with an answer. I have a long list of names which sometimes occur more than once. I need to tally the names that DO appear 2 or more times and count how many times they appear. Something like this:

george
mary
bob
bob
mary
greg
bob
bob 3
mary 2

Any help on this would be really appreciated!

[JONvdHeyden]

Lets say your list is in A2:A100

Create a helper column (lets say column B)

in B2:
=COUNTIF($A$2:A2,A2)

and copy to B100

This will give you the occurence of each name.

Now - if you wish, create another helper column and concatenate the name with the occurence #, e.g:

In C2:

=IF(B2>1,A2 & " " & B2,"")

and copy to C100

Then you can copy C2:C100 over A2:A100, using paste special > values, and then delete columns B and C.

HTH

[madd]

great thanks! I guess what I really needed though was for the list to display the greatest amount just once.

for example:
mary
mary
bob
mary
bob
george

gives me:
mary2
mary3
bob2

is there a way to just have mary 3 and bypass the counting?

[JONvdHeyden]

I think I follow

Lets say again list is in A2:A100

In B2:
=IF(COUNTIF($A$1:A1,A1)=COUNTIF(A:A,A1),A1& " " & COUNTIF(A:A,A1),A1)

and copy to B100

Copy B2:B100 and paste over A2:A100 (with paste special > values).

Then delete column B.

[martindwilson]

if you just want the most comon name and how many
assuming names in a1:100
=INDEX(A1:A100,MATCH(MAX(COUNTIF(A1:A100,A1:A100)),COUNTIF(A1:A100,A1:A100),0))
array entered (with ctrl+shift+enter so {} appear around it) will return the name
and
=COUNTIF(A1:A100,INDEX(A1:A100,MATCH(MAX(COUNTIF(A1:A100,A1:A100)),COUNTIF(A1:A100,A1:A100),0))) again array entered will tell you how many
if there is a tie only the first one found will be shown.

[madd]

Jon is definitely on the right track.

Sorry, I'll try to explain myself a little better. That works perfectly except it still shows single instances for example... Mary1. So I guess 2 and above but also like you have just done- only show the highest number. Sorry, really bad at explaining, apparently.

[JONvdHeyden]

Hi Madd

My last suggestion should only index the final occurence with the occurence #. See the attached example.

[madd]

Well, thanks so much for your help. For some reason the formulas didn't show up in the .xls but that is exactly what I was needing. I'll try to work off of what you gave me before.
Thanks again!