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
news
> 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
> news
> > 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?
>>
>.
>