Counting Repeated text or duplicates in a list

  • Thread starter Thread starter Guest
  • Start date Start date
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 !
 
=COUNTA(A1:A50)-SUMPRODUCT((A1:A50<>"")/COUNTIF(A1:A50,A1:A50&""))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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's formula returns 3 (the number of repeat values) with your data.

What does "doesn't seem to be working" mean to you?
 
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.
 
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

Counting number of items excluding duplicates. 1
Need to count repeates in a very large list of cells with text 2
count different instances in a list 2
Count quantity of repeated items in a column 4
How to use pivot table to count repeats of unique fields 4
Formula for Avoiding totals of Duplicate Information 1
Counting Cells, excluding those which have appeared before. 3
Counting text which meets a criteria 2

Back
Top