Thread: Filter Dupes to a separate sheet

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

"bondg" <bondg_AT_stcsc.edu> wrote in message
news:ONKSrCI4FHA.3880@TK2MSFTNGP12.phx.gbl...
>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
>

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" <bondg_AT_stcsc.edu> wrote in message
news:ONKSrCI4FHA.3880@TK2MSFTNGP12.phx.gbl...
> 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
>
>

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" <artf1@verizon.net> wrote in message
news:WUraf.5381$9E4.945@trndny02...
> 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" <bondg_AT_stcsc.edu> wrote in message
> news:ONKSrCI4FHA.3880@TK2MSFTNGP12.phx.gbl...
>> 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
>>
>>
>
>

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

"bondg" <bondg_AT_stcsc.edu> wrote in message
news:e7hyVJL4FHA.1416@TK2MSFTNGP09.phx.gbl...
> 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" <artf1@verizon.net> wrote in message
> news:WUraf.5381$9E4.945@trndny02...
> > 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" <bondg_AT_stcsc.edu> wrote in message
> > news:ONKSrCI4FHA.3880@TK2MSFTNGP12.phx.gbl...
> >> 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
> >>
> >>
> >
> >
>
>

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


"Art Farrell" <artf1@verizon.net> wrote in message
news:hpyaf.1632$fF3.1629@trndny04...
> 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
>
> "bondg" <bondg_AT_stcsc.edu> wrote in message
> news:e7hyVJL4FHA.1416@TK2MSFTNGP09.phx.gbl...
> > 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" <artf1@verizon.net> wrote in message
> > news:WUraf.5381$9E4.945@trndny02...
> > > 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" <bondg_AT_stcsc.edu> wrote in message
> > > news:ONKSrCI4FHA.3880@TK2MSFTNGP12.phx.gbl...
> > >> 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
> > >>
> > >>
> > >
> > >
> >
> >
>
>

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

"Art Farrell" <artf1@verizon.net> wrote in message
news:EKyaf.6039$9E4.5836@trndny02...
> 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
>
>
> "Art Farrell" <artf1@verizon.net> wrote in message
> news:hpyaf.1632$fF3.1629@trndny04...
>> 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
>>
>> "bondg" <bondg_AT_stcsc.edu> wrote in message
>> news:e7hyVJL4FHA.1416@TK2MSFTNGP09.phx.gbl...
>> > 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" <artf1@verizon.net> wrote in message
>> > news:WUraf.5381$9E4.945@trndny02...
>> > > 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" <bondg_AT_stcsc.edu> wrote in message
>> > > news:ONKSrCI4FHA.3880@TK2MSFTNGP12.phx.gbl...
>> > >> 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
>> > >>
>> > >>
>> > >
>> > >
>> >
>> >
>>
>>
>
>