Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Repeatdude
 
Posts: n/a
Default 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 !

  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

=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
...
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 !



  #3   Report Post  
Repeatdude
 
Posts: n/a
Default

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




  #4   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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   Report Post  
Repeatdude
 
Posts: n/a
Default

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   Report Post  
biff
 
Posts: n/a
Default

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?


.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to count matching text Duplicateman Excel Discussion (Misc queries) 6 November 26th 04 09:40 AM


All times are GMT +1. The time now is 07:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"