Filter Dupes to a separate sheet

[bondg]

I have a sheet full of contacts (5300+ records). Many of these contacts
work at the same company (e.g., I have 9 BMW employees listed). I need to
filter this list to another location wherein I'll have ONLY those companies
that are duplicated, and have each of these heretofore duplicated names
appear only once. All non-duplicated companies can be left behind.

Can anyone help me out? I can't seem to figure out how to keep just one
instance of each duplicated name.

TIA

 

[bondg]

I left out what is probably an important detail.... I can't use "Unique
Records Only" because out of 25 columns only a few are duplicated
(AccountName, Phone, Address....). So according to Excel, the records are
not duplicated.

bondg

 

[Art Farrell]

Hi bondg,

This will put a list of the nonduplicated companies on a second sheet. I've
assumed your company names are in column B- you can change accordingly, and
also change the Criteria location if you have other data there.

1- Put this formula in M2. =COUNTIF($B$2:B2,B2)=1
2- With your data on Sheet1 open a new Sheet, Sheet2.
3- On this sheet go to Data > Filter > Advanced Filter
4- In the dialog box select 'Copy to another location'
5- Tab to the next box and select sheet1 and select all of your database.
6- Tab to the next box and select Criteria Range, say M1:M2
7- Tab to Copy To and select Sheet2, A1.
8- Select OK. Your filtered results will be on Sheet2

If you want this or modifications in a macro send me an email.

CHORDially,
Art Farrell

 

[bondg]

Thanks so much for your help. It took me about 8 tries to make it work (I
failed to notice the criteria range you specified), but it finally did.

However, (don't you just hate 'however'??), I need to copy over ONLY the
names that are duped. In other words, if I have the below list:

A Company | Joe Mama | 123-4567
B Company | Jack Anjihl | 234-5678
B Company | Jenny Thalia | 345-6789
C Company | Jon Athon | 456-7890

....I want my new list to be:

B Company | Jack Anjihl | 234-5678.

This will result in my having a list of only those companies for which we
have multiple contact persons. I'm looking into how to modify what you've
provided me into what I need. If you're up for another go at it, I'm all
ears!

bondg

Hi bondg,

This will put a list of the nonduplicated companies on a second sheet.
I've
assumed your company names are in column B- you can change accordingly,
and
also change the Criteria location if you have other data there.

1- Put this formula in M2. =COUNTIF($B$2:B2,B2)=1
2- With your data on Sheet1 open a new Sheet, Sheet2.
3- On this sheet go to Data > Filter > Advanced Filter
4- In the dialog box select 'Copy to another location'
5- Tab to the next box and select sheet1 and select all of your database.
6- Tab to the next box and select Criteria Range, say M1:M2
7- Tab to Copy To and select Sheet2, A1.
8- Select OK. Your filtered results will be on Sheet2

If you want this or modifications in a macro send me an email.

CHORDially,
Art Farrell

 

[Art Farrell]

Hi,

If you only want one of the dup's and you aren't particular whether it's the
first or second occurrence you can just modify the Criteria formuls to:

=COUNTIF($B$2:B2,B2)=2

If this isn't OK write back and we'll try another way.

CHORDially,
Art Farrell

Thanks so much for your help. It took me about 8 tries to make it work (I
failed to notice the criteria range you specified), but it finally did.

However, (don't you just hate 'however'??), I need to copy over ONLY the
names that are duped. In other words, if I have the below list:

A Company | Joe Mama | 123-4567
B Company | Jack Anjihl | 234-5678
B Company | Jenny Thalia | 345-6789
C Company | Jon Athon | 456-7890

...I want my new list to be:

B Company | Jack Anjihl | 234-5678.

This will result in my having a list of only those companies for which we
have multiple contact persons. I'm looking into how to modify what you've
provided me into what I need. If you're up for another go at it, I'm all
ears!

bondg

 

[Art Farrell]

Hi again,

If you do want the first occurrence use this as the Criteria:

=AND(COUNTIF($B$2:B3,B3)=2,COUNTIF($B$2:B2,B2)=1)

CHORDially,
Art Farrell

 

[bondg]

Damn, you're good!! That's the formula I need. Thank you so much, Art.

Now if I can just decipher what it means and how it works. Actually,
I've got a good feel for why it works, but I'm sure I'd never have come up
with it on my own.

Thank you again for your help.

bondg