Finding duplicates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ted Metro - 21 Nov 2005 17:19 GMT

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.

Reply to this Message

David Billigmeier - 21 Nov 2005 18:45 GMT

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

Signature

Regards,
Dave

> I have a large customer list and am looking for duplicates that may be a
> variant spelling.
[quoted text clipped - 16 lines]
> forumla to look for those 4 letters anywhere in the name, instead of an exact
> match.

Reply to this Message

Ted Metro - 21 Nov 2005 19:09 GMT

Thanks David, that works great!!!

> 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
[quoted text clipped - 23 lines]
> > forumla to look for those 4 letters anywhere in the name, instead of an exact
> > match.

Reply to this Message