If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Counting Repeated text or duplicates in a list
 Author Name Remember Me? Password
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Counting Repeated text or duplicates in a list

 Thread Tools Display Modes
#1
November 26th 04, 05:05 PM
 Repeatdude external usenet poster 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 !

#2
November 26th 04, 05:43 PM
 Bob Phillips external usenet poster 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 !
>

#3
November 26th 04, 06:07 PM
 Repeatdude external usenet poster 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 !
> >

>
>
>

#4
November 26th 04, 06:37 PM
 JE McGimpsey external usenet poster 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?

#5
November 26th 04, 06:51 PM
 Repeatdude external usenet poster 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?

>

#6
November 26th 04, 07:10 PM
 biff external usenet poster 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 Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

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

All times are GMT +1. The time now is 05:23 AM.

 - Contact Us - ExcelBanter forum home - FAQ - Links - Privacy Statement - Top