Searching Two Not-That-Similar Lists

S

SamuelT

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
 
T

Toppers

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
 
S

Scoops

SamuelT said:
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
Click to expand...

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
 
Top