"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?

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?

How can i get the pivot table example you gave me.. to equal Grand Total =3
Since only 3 clients came in more than once, instead of the total is giving
me now 17.

I dont want the names that egual 1 visit to be counted towards the grand
total. only the repeats.



"Gary''s Student" wrote:

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 Doe1
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?