Counting Repeated text or duplicates in a list
 

I need a simple formula to count the total number of repeated text in a list
(column). Say there is thousands of entries (confirmation numbers) both
numeric, alphabetic and alpha-numeric but many of the entries are repeats.
How can I calculate in one cell the total number of repeated instances in the
list? And I can't sort the list, just calculate the number of repeated
instances in the list just the way it is, and the list is still growing, so I
need the formula to be able to count all instances in the list even as they
are being added. plz help !

=COUNTA(A1:A50)-SUMPRODUCT((A1:A50<"")/COUNTIF(A1:A50,A1:A50&""))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Repeatdude" wrote in message
...
I need a simple formula to count the total number of repeated text in a
list
(column). Say there is thousands of entries (confirmation numbers) both
numeric, alphabetic and alpha-numeric but many of the entries are repeats.
How can I calculate in one cell the total number of repeated instances in
the
list? And I can't sort the list, just calculate the number of repeated
instances in the list just the way it is, and the list is still growing,
so I
need the formula to be able to count all instances in the list even as
they
are being added. plz help !

Doesn't seem to be working. Let's say I have the following "case numbers" or
"confirmation numbers":
RTG1100
RTG1101
RTG1102
HJR575
RTG1101
RTG1102
498877
235754
GYU33356
498877
There are 10 entries above, but 3 of them are repeats. How can that be
calculated? My real lists are several thousand confirmation numbers and
about 50% of them are repeats somewhere in the lists. I need a formula that
can look at an entire column and tell me how many of the confirmation numbers
are the same.

any other ideas?
"Bob Phillips" wrote:

=COUNTA(A1:A50)-SUMPRODUCT((A1:A50<"")/COUNTIF(A1:A50,A1:A50&""))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Repeatdude" wrote in message
...
I need a simple formula to count the total number of repeated text in a
list
(column). Say there is thousands of entries (confirmation numbers) both
numeric, alphabetic and alpha-numeric but many of the entries are repeats.
How can I calculate in one cell the total number of repeated instances in
the
list? And I can't sort the list, just calculate the number of repeated
instances in the list just the way it is, and the list is still growing,
so I
need the formula to be able to count all instances in the list even as
they
are being added. plz help !

Bob's formula returns 3 (the number of repeat values) with your data.

What does "doesn't seem to be working" mean to you?


In article ,
"Repeatdude" wrote:

any other ideas?

Sorry about that...I just tried it again and it is working. The first time I
tried it I was getting a value of "1" no matter what I was putting in the
list. My bad....Bob's formula did the trick afterall.

Thank you kindly.

"JE McGimpsey" wrote:

Bob's formula returns 3 (the number of repeat values) with your data.

What does "doesn't seem to be working" mean to you?


In article ,
"Repeatdude" wrote:

any other ideas?

Did you see the replies to your post from last night?

Biff

-----Original Message-----
Sorry about that...I just tried it again and it is
working. The first time I
tried it I was getting a value of "1" no matter what I
was putting in the
list. My bad....Bob's formula did the trick afterall.

Thank you kindly.

"JE McGimpsey" wrote:

Bob's formula returns 3 (the number of repeat values)
with your data.

What does "doesn't seem to be working" mean to you?


In article <9B856998-8245-4E86-AE71-
,
"Repeatdude"
wrote:

any other ideas?

.