Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advance Lookup Functions
I've 2 w/sheet.
One is the correct detail and with reference number Another is a imcomplete w/sheet. What I need is to find the reference number for 2 w/sheet if some of the criteria match the first sheet. Example: W/Sheet One. Ref Name Add City Country 123 XYX Hotel 27 W. Street NYC USA 125 ABC Hotel Hannover St LON UK W/Sheet Two Ref Name Add City Country ABC LON UK So what I need is for w/sheet two to return the reference based on sheet one if the following criteria is match: 1. Country - Exact Match 2. City - Match at least 2-3 letters 3. Address - Match at least 5 letters (if ok if there is no value) 4. Name - Match at least 5 letters Not sure if that's possible. I've more than 100k row in the first sheet. So it will take ages to find one by one. Trying to find easies way. Hope to find solution soon. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advance Lookup Functions
Since you mentioned having over 100k rows, I'm assuming you're using XL 2007.
Here's a start: =INDEX(Sheet1!A:A,SUMPRODUCT(ROW(Sheet1!B:B),--(Sheet1!E:E=Sheet2!E2),--(LEFT(Sheet1!D:D,3)=LEFT(D2,3)),--(LEFT(Sheet1!B:B,5)=LEFT(B2,5)),((LEFT(C2,5)=LEFT( Sheet1!C:C,5))+ISBLANK(C2)))) A comment: You said that name needs to match "at least 5 letters" but your example only has 3 listed on 2nd worksheet. The current formula is written to check first 5 characters, but if you really meant only 3, it will need to be changed (the LEFT functions dealing with column B) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kim" wrote: I've 2 w/sheet. One is the correct detail and with reference number Another is a imcomplete w/sheet. What I need is to find the reference number for 2 w/sheet if some of the criteria match the first sheet. Example: W/Sheet One. Ref Name Add City Country 123 XYX Hotel 27 W. Street NYC USA 125 ABC Hotel Hannover St LON UK W/Sheet Two Ref Name Add City Country ABC LON UK So what I need is for w/sheet two to return the reference based on sheet one if the following criteria is match: 1. Country - Exact Match 2. City - Match at least 2-3 letters 3. Address - Match at least 5 letters (if ok if there is no value) 4. Name - Match at least 5 letters Not sure if that's possible. I've more than 100k row in the first sheet. So it will take ages to find one by one. Trying to find easies way. Hope to find solution soon. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advance Lookup Functions
Hi Luke,
Yes I'm using Excel 2007. I'm doing a simple example. If I want to match more, I just need to change the number? And what if my match is not from the begining. Example: Sheet 1 - Marriott Marble Arch Sheet 2 - Marble Arch Marriott Regards, Kim "Luke M" wrote: Since you mentioned having over 100k rows, I'm assuming you're using XL 2007. Here's a start: =INDEX(Sheet1!A:A,SUMPRODUCT(ROW(Sheet1!B:B),--(Sheet1!E:E=Sheet2!E2),--(LEFT(Sheet1!D:D,3)=LEFT(D2,3)),--(LEFT(Sheet1!B:B,5)=LEFT(B2,5)),((LEFT(C2,5)=LEFT( Sheet1!C:C,5))+ISBLANK(C2)))) A comment: You said that name needs to match "at least 5 letters" but your example only has 3 listed on 2nd worksheet. The current formula is written to check first 5 characters, but if you really meant only 3, it will need to be changed (the LEFT functions dealing with column B) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kim" wrote: I've 2 w/sheet. One is the correct detail and with reference number Another is a imcomplete w/sheet. What I need is to find the reference number for 2 w/sheet if some of the criteria match the first sheet. Example: W/Sheet One. Ref Name Add City Country 123 XYX Hotel 27 W. Street NYC USA 125 ABC Hotel Hannover St LON UK W/Sheet Two Ref Name Add City Country ABC LON UK So what I need is for w/sheet two to return the reference based on sheet one if the following criteria is match: 1. Country - Exact Match 2. City - Match at least 2-3 letters 3. Address - Match at least 5 letters (if ok if there is no value) 4. Name - Match at least 5 letters Not sure if that's possible. I've more than 100k row in the first sheet. So it will take ages to find one by one. Trying to find easies way. Hope to find solution soon. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advance Lookup Functions
Hi Luke,
Was testing the formula but it doesnt work. It give me the first ref number when the Country is matched. What I need is the country need to be exact match, then if have to check if the city is match (at least 3 letters), then check the address and follow by the name. If it match then it return the ref number. If it doesn't match then it will be blank. Regards, Kim "Luke M" wrote: Since you mentioned having over 100k rows, I'm assuming you're using XL 2007. Here's a start: =INDEX(Sheet1!A:A,SUMPRODUCT(ROW(Sheet1!B:B),--(Sheet1!E:E=Sheet2!E2),--(LEFT(Sheet1!D:D,3)=LEFT(D2,3)),--(LEFT(Sheet1!B:B,5)=LEFT(B2,5)),((LEFT(C2,5)=LEFT( Sheet1!C:C,5))+ISBLANK(C2)))) A comment: You said that name needs to match "at least 5 letters" but your example only has 3 listed on 2nd worksheet. The current formula is written to check first 5 characters, but if you really meant only 3, it will need to be changed (the LEFT functions dealing with column B) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kim" wrote: I've 2 w/sheet. One is the correct detail and with reference number Another is a imcomplete w/sheet. What I need is to find the reference number for 2 w/sheet if some of the criteria match the first sheet. Example: W/Sheet One. Ref Name Add City Country 123 XYX Hotel 27 W. Street NYC USA 125 ABC Hotel Hannover St LON UK W/Sheet Two Ref Name Add City Country ABC LON UK So what I need is for w/sheet two to return the reference based on sheet one if the following criteria is match: 1. Country - Exact Match 2. City - Match at least 2-3 letters 3. Address - Match at least 5 letters (if ok if there is no value) 4. Name - Match at least 5 letters Not sure if that's possible. I've more than 100k row in the first sheet. So it will take ages to find one by one. Trying to find easies way. Hope to find solution soon. Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advance Lookup Functions
What you are dealing with is unstrucured data. Any formulas that we can give
you are not going to work too well as your lookups are too fuzzy. You are best off to attack the sheet in pieces and structure the data as much as possible. For example using the search / find functions find all of the Marriot hotels and add references that match your first sheet. Standardize the easiest things first like country and city. Then group those together to make standardizing hotel names and such easier. Personal names will be the most difficult as you may need to match Will Smith with Bill Smith with W. Smith with B. Smith. -- HTH... Jim Thomlinson "Kim" wrote: Hi Luke, Yes I'm using Excel 2007. I'm doing a simple example. If I want to match more, I just need to change the number? And what if my match is not from the begining. Example: Sheet 1 - Marriott Marble Arch Sheet 2 - Marble Arch Marriott Regards, Kim "Luke M" wrote: Since you mentioned having over 100k rows, I'm assuming you're using XL 2007. Here's a start: =INDEX(Sheet1!A:A,SUMPRODUCT(ROW(Sheet1!B:B),--(Sheet1!E:E=Sheet2!E2),--(LEFT(Sheet1!D:D,3)=LEFT(D2,3)),--(LEFT(Sheet1!B:B,5)=LEFT(B2,5)),((LEFT(C2,5)=LEFT( Sheet1!C:C,5))+ISBLANK(C2)))) A comment: You said that name needs to match "at least 5 letters" but your example only has 3 listed on 2nd worksheet. The current formula is written to check first 5 characters, but if you really meant only 3, it will need to be changed (the LEFT functions dealing with column B) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kim" wrote: I've 2 w/sheet. One is the correct detail and with reference number Another is a imcomplete w/sheet. What I need is to find the reference number for 2 w/sheet if some of the criteria match the first sheet. Example: W/Sheet One. Ref Name Add City Country 123 XYX Hotel 27 W. Street NYC USA 125 ABC Hotel Hannover St LON UK W/Sheet Two Ref Name Add City Country ABC LON UK So what I need is for w/sheet two to return the reference based on sheet one if the following criteria is match: 1. Country - Exact Match 2. City - Match at least 2-3 letters 3. Address - Match at least 5 letters (if ok if there is no value) 4. Name - Match at least 5 letters Not sure if that's possible. I've more than 100k row in the first sheet. So it will take ages to find one by one. Trying to find easies way. Hope to find solution soon. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help on lookup functions | Excel Discussion (Misc queries) | |||
advance lookup and paste | Excel Discussion (Misc queries) | |||
Lookup functions | Excel Worksheet Functions | |||
LOOKUP functions? | Excel Worksheet Functions | |||
Lookup functions | Excel Discussion (Misc queries) |