I need a macro instead of a formula.
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?
|