I have a large customer list and am looking for duplicates that may be a
variant spelling.

So assume I have two lists like this starting in A1

Cattail Supply
Dogwood Rentals
Tigerland Farms

Dogwood Inc.
Dove Ltd.

With the second list in B5 I'd like a formula that would take the first 4
letters of A5 and see if there is a name in a1:a3 that has that string of 4
letters anywhere in the name, and if so populate a 1.

For an exact match I can do something like isna(match(a5,a1:a3,false)) and I
could surely change a5 to left(a5,4), but I can figure out how to write the
forumla to look for those 4 letters anywhere in the name, instead of an exact
match.

Array entered (CTRL+SHIFT+ENTER), the following formula will count the number
of cells in the range B1:B10 that have the left 4 characters of cell A5
appear anywhere in each string. Change the cell references to fit your
specific data:

=SUM(IF(ISERROR(SEARCH(LEFT(A5,4),B1:B10)),0,1))


--
Regards,
Dave


"Ted Metro" wrote:

I have a large customer list and am looking for duplicates that may be a
variant spelling.

So assume I have two lists like this starting in A1

Cattail Supply
Dogwood Rentals
Tigerland Farms

Dogwood Inc.
Dove Ltd.

With the second list in B5 I'd like a formula that would take the first 4
letters of A5 and see if there is a name in a1:a3 that has that string of 4
letters anywhere in the name, and if so populate a 1.

For an exact match I can do something like isna(match(a5,a1:a3,false)) and I
could surely change a5 to left(a5,4), but I can figure out how to write the
forumla to look for those 4 letters anywhere in the name, instead of an exact
match.

Thanks David, that works great!!!

"David Billigmeier" wrote:

Array entered (CTRL+SHIFT+ENTER), the following formula will count the number
of cells in the range B1:B10 that have the left 4 characters of cell A5
appear anywhere in each string. Change the cell references to fit your
specific data:

=SUM(IF(ISERROR(SEARCH(LEFT(A5,4),B1:B10)),0,1))


--
Regards,
Dave


"Ted Metro" wrote:

I have a large customer list and am looking for duplicates that may be a
variant spelling.

So assume I have two lists like this starting in A1

Cattail Supply
Dogwood Rentals
Tigerland Farms

Dogwood Inc.
Dove Ltd.

With the second list in B5 I'd like a formula that would take the first 4
letters of A5 and see if there is a name in a1:a3 that has that string of 4
letters anywhere in the name, and if so populate a 1.

For an exact match I can do something like isna(match(a5,a1:a3,false)) and I
could surely change a5 to left(a5,4), but I can figure out how to write the
forumla to look for those 4 letters anywhere in the name, instead of an exact
match.