Home 
Search 
Today's Posts 
#1




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 alphanumeric 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 ! 
#2




=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 alphanumeric 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 ! 
#3




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 alphanumeric 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 ! 
#4




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? 
#5




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? 
#6




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 <9B85699882454E86AE71 , "Repeatdude" wrote: any other ideas? . 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How to count matching text  Excel Discussion (Misc queries) 