Counting Repeated text or duplicates in a list

G

Guest

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 !
 
B

Bob Phillips

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

--

HTH

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

Guest

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

JE McGimpsey

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

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

Guest

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.
 
B

biff

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 said:
Bob's formula returns 3 (the number of repeat values) with your data.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count different instances in a list 2
Count quantity of repeated items in a column 4
Counting number of items excluding duplicates. 1
Problem not counting duplicates 1
How to use pivot table to count repeats of unique fields 4
MAKE A LIST OF NAMES FROM REPEATED NAMES IN THE SAME WORKSHEET 2
Consecutive repetitions 10
How to count matching text 6

Top