Searching Two Not-That-Similar Lists

[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

[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

"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
>
>

[Scoops]

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