I need a macro instead of a formula.
You don't need a macro; you need a Pivot Table. Say the data is:
item
Curly
Bill Gates
Moe
Bill Gates
Trevor
David
Chris Max
John Doe
Chris Max
Max
joe
Wilber
joe
Chris Max
Larry
joe
Victor
A variation of your formula:
=SUMPRODUCT((A2:A101<"")/COUNTIF(A2:A101,A2:A101&""))-SUMPRODUCT(--(COUNTIF(A2:A101,A2:A101)=1))
will produce 3 as there are three names that have repeats.
A Pivot Table of the same data produces:
Count of item
item Total
Bill Gates 2
Chris Max 3
Curly 1
David 1
joe 3
John Doe 1
Larry 1
Max 1
Moe 1
Trevor 1
Victor 1
Wilber 1
Grand Total 17
Now you see the items that are repeated and how many times they are repeated.
--
Gary''s Student - gsnu200776
"Jman" wrote:
"Jman" wrote: I need a macro that counts duplicates names only.
I have used this formula and it works well until after 1000 cells down it
causes excel to freeze.
=SUMPRODUCT((A1:A65530<"")/COUNTIF(A1:A65530,A1:A65530&""))-SUMPRODUCT(--(COUNTIF(A1:A65530,A1:A65530)=1))
John Doe
Bill Gates
Bill Gates
Chris Max
Chris Max
Chris Max
I want the end result to be = 2 repeats.
Is there a macro that does the same thing?
|