Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching Two Not-That-Similar Lists
Hi all, I have two worksheets. One which has a list of properties, the other has a list of the same properties, but as their Autocad drawing file names. I believe that not all the properties have drawings, so I'm trying to run a search that displays which properties do. However due to the drawing name (e.g. Hoddesden GF As Fixed (29245) MVS.dwg) being different to the property name (e.g. Hoddesden) I am not entirely sure how I go about doing this. If it's of any use, the property name is always the first thing to appear before the drawing extension details. Can anyone suggest a solution? I've tried a couple of IF(VLOOKUP)-type functions but always get stumped when it comes to getting Excel to ignore everything after the property name. Any help greatly appreciated. TIA, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501 View this thread: http://www.excelforum.com/showthread...hreadid=566659 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching Two Not-That-Similar Lists
Will this help?
Place in sheet containing drawing names and assuming properties are in column A on "Sheet2" and CAD names are in column A: =IF(ISERROR(MATCH(LEFT(A1,FIND(" ",A1)-1),Sheet2!A:A,0)),"No match","Matched") This will match the property name in CAD drawing with property name AND assumes a blank as delimeter of the property name in the CAD name. Copy down as required HTH "SamuelT" wrote: Hi all, I have two worksheets. One which has a list of properties, the other has a list of the same properties, but as their Autocad drawing file names. I believe that not all the properties have drawings, so I'm trying to run a search that displays which properties do. However due to the drawing name (e.g. Hoddesden GF As Fixed (29245) MVS.dwg) being different to the property name (e.g. Hoddesden) I am not entirely sure how I go about doing this. If it's of any use, the property name is always the first thing to appear before the drawing extension details. Can anyone suggest a solution? I've tried a couple of IF(VLOOKUP)-type functions but always get stumped when it comes to getting Excel to ignore everything after the property name. Any help greatly appreciated. TIA, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501 View this thread: http://www.excelforum.com/showthread...hreadid=566659 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching Two Not-That-Similar Lists
SamuelT wrote: Hi all, I have two worksheets. One which has a list of properties, the other has a list of the same properties, but as their Autocad drawing file names. I believe that not all the properties have drawings, so I'm trying to run a search that displays which properties do. However due to the drawing name (e.g. Hoddesden GF As Fixed (29245) MVS.dwg) being different to the property name (e.g. Hoddesden) I am not entirely sure how I go about doing this. If it's of any use, the property name is always the first thing to appear before the drawing extension details. Can anyone suggest a solution? I've tried a couple of IF(VLOOKUP)-type functions but always get stumped when it comes to getting Excel to ignore everything after the property name. Any help greatly appreciated. TIA, SamuelT Hi SamuelT If your drawing text is in A1, try: =IF(VLOOKUP(LEFT(A1,FIND(" ",A1)-1)... This will look for the at the first whole word in the drawing text. You will have a problem with the likes of Hoddesdon Gardens, and Hoddesdon Plaza both returning Hoddesdon, but see if it helps. Regards Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Lists... | Excel Discussion (Misc queries) | |||
VLOOKUP and Multi Lists | New Users to Excel | |||
Excel - need a function to compare lists | Excel Worksheet Functions | |||
Alpha searching in lists. | Excel Discussion (Misc queries) |