| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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" 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 |
|
#3
|
|||
|
|||
|
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" wrote in message ... 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" 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 |
|
#4
|
|||
|
|||
|
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" wrote in message ... 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" 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 |
|
#5
|
|||
|
|||
|
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" wrote in message ... 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" wrote in message ... 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" 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 |
|
#6
|
|||
|
|||
|
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" wrote in message ... 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" wrote in message ... 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" 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 |
|
#7
|
|||
|
|||
|
Many thanks, Chuck!
"CLR" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Refresh a Validation List? | Excel Discussion (Misc queries) | |||
| Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
| How do I find out what items are in one list but not in another l. | Excel Discussion (Misc queries) | |||
| Counting Repeated text or duplicates in a list | Excel Discussion (Misc queries) | |||
| Creating a list from an existing list. | Excel Worksheet Functions | |||
Linear Mode
