software

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
gt; System1: ABC Construction

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

In X,

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

Put in C1, array-enter (press CTRL SHIFT ENTER):
=IF(B1=quot;quot;,quot;quot;,IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH(B1 ,Y!$A$1:$A$100)),0)),quot;quot;,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 quot;matchedquot; 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
savefile.com/projects/236895
xdemechanik
---
quot;Samanthaquot; gt; wrote in message
...
gt; Is there a way to match a name that's not exact? I have 2 systems that are
gt; independently run, but they both have Vendor ID and Vendor Name. The
problem
gt; is System1's Vendor ID is NOT the same as System2's Vendor ID. There is
gt; partial commonality on the Vendor Name, but not all.
gt; For example, the Vendor Name might be:
gt; System1: ABC Construction
gt; System2: ABC-Construction
gt; or any variation, depending on the creativity of the person entering data.
gt;
gt; Is there a function or way to match the Vendor Name and Vendor ID from
both
gt; list, as close as possible in Excel, so as to limit the manual labor to
this
gt; long list? Thanks in advance.
On Mon, 10 Apr 2006 16:13:02 -0700, Samantha
gt; wrote:

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

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