Remember Me?

 Repeatdude Posts: n/a 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 !

 Bob Phillips Posts: n/a =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 !

 Repeatdude Posts: n/a 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 !

 JE McGimpsey Posts: n/a 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?

 Repeatdude Posts: n/a 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?

 biff Posts: n/a 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)

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

In article <9B856998-8245-4E86-AE71-

,
"Repeatdude"

wrote:

any other ideas?

.

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Duplicateman Excel Discussion (Misc queries) 6 November 26th 04 09:40 AM

All times are GMT +1. The time now is 03:48 AM. Copyright ©2004-2020 ExcelBanter.