Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Address book worksheet problem
I have two long worksheets that serve as address lists.
My problem is I believe there is duplication on the lists. Here's an example of the data Full Name First Last Salutation Home Address BusAddress Sponsor John Doe John Doe Mr. 4 Bird Lane 5 Spire St. N Jill Xo Jill Xo Ms. 2 Quite St None Y I need to check for any duplication in the address fields and then somehow flag the field in the other worksheet. So I may need to check "4Bird Lane" agains the entire address field in the other worksheet. I don't really want to combine the sheets because the column fields are not all the same and are used for different purposes. Is this a lookup or match command? I'm not sure? Help -- sharon |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Address book worksheet problem
Without more specifics, this basic formula, copied down a helper column,
will find the duplicates........change the cells accordingly. =VLOOKUP(A1,Sheet2!D:D,1,FALSE) But, you still will probably have other duplicates that this method will not find......and those are due to the fact that the addresses may be entered differently on different rows, such as 4 Bird Lane, 4Bird Lane, 4 Bird Ln., etc etc......... hth Vaya con Dios, Chuck, CABGx3 "sharon" wrote: I have two long worksheets that serve as address lists. My problem is I believe there is duplication on the lists. Here's an example of the data Full Name First Last Salutation Home Address BusAddress Sponsor John Doe John Doe Mr. 4 Bird Lane 5 Spire St. N Jill Xo Jill Xo Ms. 2 Quite St None Y I need to check for any duplication in the address fields and then somehow flag the field in the other worksheet. So I may need to check "4Bird Lane" agains the entire address field in the other worksheet. I don't really want to combine the sheets because the column fields are not all the same and are used for different purposes. Is this a lookup or match command? I'm not sure? Help -- sharon |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Address book worksheet problem
Thanks.
Any ideas on how to address the second issue, with the addresses being close to matches but not exact: 4 Bird Lane or 4 Bird Ln. Is there any way to use an * or something? -- sharon "CLR" wrote: Without more specifics, this basic formula, copied down a helper column, will find the duplicates........change the cells accordingly. =VLOOKUP(A1,Sheet2!D:D,1,FALSE) But, you still will probably have other duplicates that this method will not find......and those are due to the fact that the addresses may be entered differently on different rows, such as 4 Bird Lane, 4Bird Lane, 4 Bird Ln., etc etc......... hth Vaya con Dios, Chuck, CABGx3 "sharon" wrote: I have two long worksheets that serve as address lists. My problem is I believe there is duplication on the lists. Here's an example of the data Full Name First Last Salutation Home Address BusAddress Sponsor John Doe John Doe Mr. 4 Bird Lane 5 Spire St. N Jill Xo Jill Xo Ms. 2 Quite St None Y I need to check for any duplication in the address fields and then somehow flag the field in the other worksheet. So I may need to check "4Bird Lane" agains the entire address field in the other worksheet. I don't really want to combine the sheets because the column fields are not all the same and are used for different purposes. Is this a lookup or match command? I'm not sure? Help -- sharon |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Address book worksheet problem
I've tried the formula and I get the first part, entering the lookup cell and
the array to search. I dont really understand the 1,FALSE part of the formula and where these should reference. -- sharon "sharon" wrote: Thanks. Any ideas on how to address the second issue, with the addresses being close to matches but not exact: 4 Bird Lane or 4 Bird Ln. Is there any way to use an * or something? -- sharon "CLR" wrote: Without more specifics, this basic formula, copied down a helper column, will find the duplicates........change the cells accordingly. =VLOOKUP(A1,Sheet2!D:D,1,FALSE) But, you still will probably have other duplicates that this method will not find......and those are due to the fact that the addresses may be entered differently on different rows, such as 4 Bird Lane, 4Bird Lane, 4 Bird Ln., etc etc......... hth Vaya con Dios, Chuck, CABGx3 "sharon" wrote: I have two long worksheets that serve as address lists. My problem is I believe there is duplication on the lists. Here's an example of the data Full Name First Last Salutation Home Address BusAddress Sponsor John Doe John Doe Mr. 4 Bird Lane 5 Spire St. N Jill Xo Jill Xo Ms. 2 Quite St None Y I need to check for any duplication in the address fields and then somehow flag the field in the other worksheet. So I may need to check "4Bird Lane" agains the entire address field in the other worksheet. I don't really want to combine the sheets because the column fields are not all the same and are used for different purposes. Is this a lookup or match command? I'm not sure? Help -- sharon |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Address book worksheet problem
It is much a pain........you can try adding a helper column to the list with
the formula =Left(D1,5) or 4 or some beginning characters, and then modifying the Vlookup to something equiable, like... =VLOOKUP(left(A1,4),Sheet2!C:C,1,FALSE) this may give you some matches that start out the same.........but like I said, it's a pain Vaya con Dios, Chuck, CABGx3 "sharon" wrote: Thanks. Any ideas on how to address the second issue, with the addresses being close to matches but not exact: 4 Bird Lane or 4 Bird Ln. Is there any way to use an * or something? -- sharon "CLR" wrote: Without more specifics, this basic formula, copied down a helper column, will find the duplicates........change the cells accordingly. =VLOOKUP(A1,Sheet2!D:D,1,FALSE) But, you still will probably have other duplicates that this method will not find......and those are due to the fact that the addresses may be entered differently on different rows, such as 4 Bird Lane, 4Bird Lane, 4 Bird Ln., etc etc......... hth Vaya con Dios, Chuck, CABGx3 "sharon" wrote: I have two long worksheets that serve as address lists. My problem is I believe there is duplication on the lists. Here's an example of the data Full Name First Last Salutation Home Address BusAddress Sponsor John Doe John Doe Mr. 4 Bird Lane 5 Spire St. N Jill Xo Jill Xo Ms. 2 Quite St None Y I need to check for any duplication in the address fields and then somehow flag the field in the other worksheet. So I may need to check "4Bird Lane" agains the entire address field in the other worksheet. I don't really want to combine the sheets because the column fields are not all the same and are used for different purposes. Is this a lookup or match command? I'm not sure? Help -- sharon |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Address book worksheet problem
1 means to return reference from the same column where the Vlookup finds the
value it's seeking, "FALSE", tells the Vlookup formula not to return any value except for an exact match....leave them both just as in the sample, only change the cell references. hth Vaya con Dios, Chuck, CABGx3 "sharon" wrote in message ... I've tried the formula and I get the first part, entering the lookup cell and the array to search. I dont really understand the 1,FALSE part of the formula and where these should reference. -- sharon "sharon" wrote: Thanks. Any ideas on how to address the second issue, with the addresses being close to matches but not exact: 4 Bird Lane or 4 Bird Ln. Is there any way to use an * or something? -- sharon "CLR" wrote: Without more specifics, this basic formula, copied down a helper column, will find the duplicates........change the cells accordingly. =VLOOKUP(A1,Sheet2!D:D,1,FALSE) But, you still will probably have other duplicates that this method will not find......and those are due to the fact that the addresses may be entered differently on different rows, such as 4 Bird Lane, 4Bird Lane, 4 Bird Ln., etc etc......... hth Vaya con Dios, Chuck, CABGx3 "sharon" wrote: I have two long worksheets that serve as address lists. My problem is I believe there is duplication on the lists. Here's an example of the data Full Name First Last Salutation Home Address BusAddress Sponsor John Doe John Doe Mr. 4 Bird Lane 5 Spire St. N Jill Xo Jill Xo Ms. 2 Quite St None Y I need to check for any duplication in the address fields and then somehow flag the field in the other worksheet. So I may need to check "4Bird Lane" agains the entire address field in the other worksheet. I don't really want to combine the sheets because the column fields are not all the same and are used for different purposes. Is this a lookup or match command? I'm not sure? Help -- sharon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert Excel Worksheet into address book in Groupwise | Excel Discussion (Misc queries) | |||
Address Book | Excel Discussion (Misc queries) | |||
How do I import Office address book to Outlook Express address bo. | Excel Discussion (Misc queries) | |||
Can I import my excel worksheet to my address book | Excel Discussion (Misc queries) | |||
address book | Excel Discussion (Misc queries) |