Winnowing a list

I have a list of names and addresses that I want to turn into mailing
labels. (bob/smith/100main/town/...)There are many instances of people
living at the same address (pam & bob & jonny/smith, sue/jones each at 100
Main).

Is there a way to extract a list of all combined multiples so there is ONE
Smith instance at 100 Main?
It would need to list if there was a Jones there too for an additional
label.

???
Thanks

You might try CONCATENATING the last names and addresses into a helper
column and sorting on that column..........this will group all the "alikes"
together.........

hth
Vaya con Dios,
Chuck, CABGx3


"NL" <[email protected]> wrote in message
news:_fQSd.7373$EL5.6416@trnddc05...
> I have a list of names and addresses that I want to turn into mailing
> labels. (bob/smith/100main/town/...)There are many instances of people
> living at the same address (pam & bob & jonny/smith, sue/jones each at 100
> Main).
>
> Is there a way to extract a list of all combined multiples so there is ONE
> Smith instance at 100 Main?
> It would need to list if there was a Jones there too for an additional
> label.
>
> ???
> Thanks
>
>

Thanks for you idea.
I couldn't figure how that would work exactly. I can just sort by address
and that groups the addresses in the list. Then I have to fiddle with each
group.
Sorry...I'm confused...

"CLR" <[email protected]> wrote in message
news:[email protected]...
> You might try CONCATENATING the last names and addresses into a helper
> column and sorting on that column..........this will group all the
> "alikes"
> together.........
>
> hth
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "NL" <[email protected]> wrote in message
> news:_fQSd.7373$EL5.6416@trnddc05...
>> I have a list of names and addresses that I want to turn into mailing
>> labels. (bob/smith/100main/town/...)There are many instances of people
>> living at the same address (pam & bob & jonny/smith, sue/jones each at
>> 100
>> Main).
>>
>> Is there a way to extract a list of all combined multiples so there is
>> ONE
>> Smith instance at 100 Main?
>> It would need to list if there was a Jones there too for an additional
>> label.
>>
>> ???
>> Thanks
>>
>>
>
>

Hi!

Sorting by Address and LastName and Firstname will get you close, probably. For this, use Data > Sort on the menu, not the sort icons.

I would be surprised if your database was so precise that a single-pass solution would be available to automate it completely.

Depending on the size, there's more you can do ranging from just looking down the list to extracting unique records using Data > Filter > Advanced filter.

Alf

Sorry.....dunno where my head was at.....First back up your data, and then of
course you can get to the same place by just doing Data > Sort, and then
setting the first key for your address column and the second key for your
last name column.........this will group all the "alikes" together......

Then, assuming your addresses are in column C and your lastnames in column
B, put this in another helper column

=IF(AND( C2=C1,B2=B1),"Duplicate","Unique")

(starting in the second cell from the top of your list), and copy
down........then sort on this column and delete all rows which say
"duplicate"......now you have only the "unique" list....

hth
Vaya con Dios,
Chuck, CABGx3



"NL" wrote:

> Thanks for you idea.
> I couldn't figure how that would work exactly. I can just sort by address
> and that groups the addresses in the list. Then I have to fiddle with each
> group.
> Sorry...I'm confused...
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > You might try CONCATENATING the last names and addresses into a helper
> > column and sorting on that column..........this will group all the
> > "alikes"
> > together.........
> >
> > hth
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "NL" <[email protected]> wrote in message
> > news:_fQSd.7373$EL5.6416@trnddc05...
> >> I have a list of names and addresses that I want to turn into mailing
> >> labels. (bob/smith/100main/town/...)There are many instances of people
> >> living at the same address (pam & bob & jonny/smith, sue/jones each at
> >> 100
> >> Main).
> >>
> >> Is there a way to extract a list of all combined multiples so there is
> >> ONE
> >> Smith instance at 100 Main?
> >> It would need to list if there was a Jones there too for an additional
> >> label.
> >>
> >> ???
> >> Thanks
> >>
> >>
> >
> >
>
>
>

That is cool!
However, it doesn't quite work. It shows the first instance of a multiple as
UNIQUE.
Hmmm. I'm thinkin on this. Thanks much for the direction.
Smith 100 Main
Smith 100 Main Duplicate
Smith 100 Main Duplicate
jones 100 Main Unique
jones 100 Main Duplicate
brown 100 Main Unique
Smith 27 Front Unique
jones 27 Front Unique
jones 27 Front Duplicate


"CLR" <[email protected]> wrote in message
news:[email protected]...
> Sorry.....dunno where my head was at.....First back up your data, and then
> of
> course you can get to the same place by just doing Data > Sort, and then
> setting the first key for your address column and the second key for your
> last name column.........this will group all the "alikes" together......
>
> Then, assuming your addresses are in column C and your lastnames in column
> B, put this in another helper column
>
> =IF(AND( C2=C1,B2=B1),"Duplicate","Unique")
>
> (starting in the second cell from the top of your list), and copy
> down........then sort on this column and delete all rows which say
> "duplicate"......now you have only the "unique" list....
>
> hth
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "NL" wrote:
>
>> Thanks for you idea.
>> I couldn't figure how that would work exactly. I can just sort by address
>> and that groups the addresses in the list. Then I have to fiddle with
>> each
>> group.
>> Sorry...I'm confused...
>>
>> "CLR" <[email protected]> wrote in message
>> news:[email protected]...
>> > You might try CONCATENATING the last names and addresses into a helper
>> > column and sorting on that column..........this will group all the
>> > "alikes"
>> > together.........
>> >
>> > hth
>> > Vaya con Dios,
>> > Chuck, CABGx3
>> >
>> >
>> > "NL" <[email protected]> wrote in message
>> > news:_fQSd.7373$EL5.6416@trnddc05...
>> >> I have a list of names and addresses that I want to turn into mailing
>> >> labels. (bob/smith/100main/town/...)There are many instances of people
>> >> living at the same address (pam & bob & jonny/smith, sue/jones each at
>> >> 100
>> >> Main).
>> >>
>> >> Is there a way to extract a list of all combined multiples so there is
>> >> ONE
>> >> Smith instance at 100 Main?
>> >> It would need to list if there was a Jones there too for an additional
>> >> label.
>> >>
>> >> ???
>> >> Thanks
>> >>
>> >>
>> >
>> >
>>
>>
>>

You're welcome, and Yup, that looks exactly like it's supposed to
look.....now, if you want ONLY the "unique's" for your mailing list, then
SAVE your data, then just do Copy > PasteSpecial > Values on that
Unique/Duplicate column and then sort on that column and then delete all
rows with "Duplicate".....

Vaya con Dios,
Chuck, CABGx3



"NL" wrote:

> That is cool!
> However, it doesn't quite work. It shows the first instance of a multiple as
> UNIQUE.
> Hmmm. I'm thinkin on this. Thanks much for the direction.
> Smith 100 Main
> Smith 100 Main Duplicate
> Smith 100 Main Duplicate
> jones 100 Main Unique
> jones 100 Main Duplicate
> brown 100 Main Unique
> Smith 27 Front Unique
> jones 27 Front Unique
> jones 27 Front Duplicate
>
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > Sorry.....dunno where my head was at.....First back up your data, and then
> > of
> > course you can get to the same place by just doing Data > Sort, and then
> > setting the first key for your address column and the second key for your
> > last name column.........this will group all the "alikes" together......
> >
> > Then, assuming your addresses are in column C and your lastnames in column
> > B, put this in another helper column
> >
> > =IF(AND( C2=C1,B2=B1),"Duplicate","Unique")
> >
> > (starting in the second cell from the top of your list), and copy
> > down........then sort on this column and delete all rows which say
> > "duplicate"......now you have only the "unique" list....
> >
> > hth
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "NL" wrote:
> >
> >> Thanks for you idea.
> >> I couldn't figure how that would work exactly. I can just sort by address
> >> and that groups the addresses in the list. Then I have to fiddle with
> >> each
> >> group.
> >> Sorry...I'm confused...
> >>
> >> "CLR" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > You might try CONCATENATING the last names and addresses into a helper
> >> > column and sorting on that column..........this will group all the
> >> > "alikes"
> >> > together.........
> >> >
> >> > hth
> >> > Vaya con Dios,
> >> > Chuck, CABGx3
> >> >
> >> >
> >> > "NL" <[email protected]> wrote in message
> >> > news:_fQSd.7373$EL5.6416@trnddc05...
> >> >> I have a list of names and addresses that I want to turn into mailing
> >> >> labels. (bob/smith/100main/town/...)There are many instances of people
> >> >> living at the same address (pam & bob & jonny/smith, sue/jones each at
> >> >> 100
> >> >> Main).
> >> >>
> >> >> Is there a way to extract a list of all combined multiples so there is
> >> >> ONE
> >> >> Smith instance at 100 Main?
> >> >> It would need to list if there was a Jones there too for an additional
> >> >> label.
> >> >>
> >> >> ???
> >> >> Thanks
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>
>

Many thanks, Chuck!

"CLR" <[email protected]> wrote in message
news:[email protected]...
> You're welcome, and Yup, that looks exactly like it's supposed to
> look.....now, if you want ONLY the "unique's" for your mailing list, then
> SAVE your data, then just do Copy > PasteSpecial > Values on that
> Unique/Duplicate column and then sort on that column and then delete all
> rows with "Duplicate".....
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "NL" wrote:
>
>> That is cool!
>> However, it doesn't quite work. It shows the first instance of a multiple
>> as
>> UNIQUE.
>> Hmmm. I'm thinkin on this. Thanks much for the direction.
>> Smith 100 Main
>> Smith 100 Main Duplicate
>> Smith 100 Main Duplicate
>> jones 100 Main Unique
>> jones 100 Main Duplicate
>> brown 100 Main Unique
>> Smith 27 Front Unique
>> jones 27 Front Unique
>> jones 27 Front Duplicate
>>
>>
>> "CLR" <[email protected]> wrote in message
>> news:[email protected]...
>> > Sorry.....dunno where my head was at.....First back up your data, and
>> > then
>> > of
>> > course you can get to the same place by just doing Data > Sort, and
>> > then
>> > setting the first key for your address column and the second key for
>> > your
>> > last name column.........this will group all the "alikes"
>> > together......
>> >
>> > Then, assuming your addresses are in column C and your lastnames in
>> > column
>> > B, put this in another helper column
>> >
>> > =IF(AND( C2=C1,B2=B1),"Duplicate","Unique")
>> >
>> > (starting in the second cell from the top of your list), and copy
>> > down........then sort on this column and delete all rows which say
>> > "duplicate"......now you have only the "unique" list....
>> >
>> > hth
>> > Vaya con Dios,
>> > Chuck, CABGx3
>> >
>> >
>> >
>> > "NL" wrote:
>> >
>> >> Thanks for you idea.
>> >> I couldn't figure how that would work exactly. I can just sort by
>> >> address
>> >> and that groups the addresses in the list. Then I have to fiddle with
>> >> each
>> >> group.
>> >> Sorry...I'm confused...
>> >>
>> >> "CLR" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > You might try CONCATENATING the last names and addresses into a
>> >> > helper
>> >> > column and sorting on that column..........this will group all the
>> >> > "alikes"
>> >> > together.........
>> >> >
>> >> > hth
>> >> > Vaya con Dios,
>> >> > Chuck, CABGx3
>> >> >
>> >> >
>> >> > "NL" <[email protected]> wrote in message
>> >> > news:_fQSd.7373$EL5.6416@trnddc05...
>> >> >> I have a list of names and addresses that I want to turn into
>> >> >> mailing
>> >> >> labels. (bob/smith/100main/town/...)There are many instances of
>> >> >> people
>> >> >> living at the same address (pam & bob & jonny/smith, sue/jones each
>> >> >> at
>> >> >> 100
>> >> >> Main).
>> >> >>
>> >> >> Is there a way to extract a list of all combined multiples so there
>> >> >> is
>> >> >> ONE
>> >> >> Smith instance at 100 Main?
>> >> >> It would need to list if there was a Jones there too for an
>> >> >> additional
>> >> >> label.
>> >> >>
>> >> >> ???
>> >> >> Thanks
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

Hi!

Data >Filter > Autofilter does a good job on picking out only the ones with Unique.
F5 > Special > Visible cells only then gives you a quick way of copying them.


Alf