Finding duplicates

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