Match name not exact

Is there a way to match a name that's not exact? I have 2 systems that are
independently run, but they both have Vendor ID and Vendor Name. The problem
is System1's Vendor ID is NOT the same as System2's Vendor ID. There is
partial commonality on the Vendor Name, but not all.
For example, the Vendor Name might be:
System1: ABC Construction
System2: ABC-Construction
or any variation, depending on the creativity of the person entering data.

Is there a function or way to match the Vendor Name and Vendor ID from both
list, as close as possible in Excel, so as to limit the manual labor to this
long list? Thanks in advance.

Just some thoughts to play with ..

Assume this list is in sheet: X in A1 down
> System1: ABC Construction

and this list is in sheet: Y, within A1:A100
> System2: ABC-Construction

In X,

Put in B1:
=IF(TRIM(A1)="","",TRIM(LEFT(A1,SEARCH(" ",A1)-1)))

Put in C1, array-enter (press CTRL+SHIFT+ENTER):
=IF(B1="","",IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH(B1,Y!$A$1:$A$100)),0)),"",IN
DEX(Y!$A$1:$A$100,MATCH(TRUE,ISNUMBER(SEARCH(B1,Y!$A$1:$A$100)),0))))

(Adapt the range Y!$A$1:$A$100 in the formula
to suit the extent of your actual data in Y)

Select B1:C1, copy down to the last row of data in col A

Col C will return the first "matched" item from Y corresponding to the
parsed leftmost* part of the name of the company in col A
*before the first space

We could also use FIND in place of SEARCH for the formula in C1 if we wanted
it to be case sensitive (SEARCH is not case sensitive)

Above might help a bit to narrow
the comparisons between the 2 lists
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Samantha" <[email protected]> wrote in message
news:[email protected]...
> Is there a way to match a name that's not exact? I have 2 systems that are
> independently run, but they both have Vendor ID and Vendor Name. The
problem
> is System1's Vendor ID is NOT the same as System2's Vendor ID. There is
> partial commonality on the Vendor Name, but not all.
> For example, the Vendor Name might be:
> System1: ABC Construction
> System2: ABC-Construction
> or any variation, depending on the creativity of the person entering data.
>
> Is there a function or way to match the Vendor Name and Vendor ID from
both
> list, as close as possible in Excel, so as to limit the manual labor to
this
> long list? Thanks in advance.

On Mon, 10 Apr 2006 16:13:02 -0700, Samantha
<[email protected]> wrote:

>Is there a way to match a name that's not exact? I have 2 systems that are
>independently run, but they both have Vendor ID and Vendor Name. The problem
>is System1's Vendor ID is NOT the same as System2's Vendor ID. There is
>partial commonality on the Vendor Name, but not all.
>For example, the Vendor Name might be:
>System1: ABC Construction
>System2: ABC-Construction
>or any variation, depending on the creativity of the person entering data.
>
>Is there a function or way to match the Vendor Name and Vendor ID from both
>list, as close as possible in Excel, so as to limit the manual labor to this
>long list? Thanks in advance.

It depends on the degree of variation in the names.

Do they all start with the same? If so, you could match the first word and a
short portion of the second, ignoring punctuation.

Or you could sort by Vendor Name, and visually inspect the results.

Or you could use a Soundex algorithm, which translates the name into a phonetic
representation.

Lots of possibilities. I think either the sorting approach might be a place to
start, at least to evaluate the degree of variation.

In addition to

ABC Construction
ABC-Construction

How about

ABC Construction Company
ABC Company
ABC
ABC, Inc

ABC Co, Inc

etc.
--ron

Try fuzzy matching:

http://www.mrexcel.com/board2/viewto...=974873#974873


Samantha wrote:
> Is there a way to match a name that's not exact? I have 2 systems that are
> independently run, but they both have Vendor ID and Vendor Name. The problem
> is System1's Vendor ID is NOT the same as System2's Vendor ID. There is
> partial commonality on the Vendor Name, but not all.
> For example, the Vendor Name might be:
> System1: ABC Construction
> System2: ABC-Construction
> or any variation, depending on the creativity of the person entering data.
>
> Is there a function or way to match the Vendor Name and Vendor ID from both
> list, as close as possible in Excel, so as to limit the manual labor to this
> long list? Thanks in advance.