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. |
Finding duplicates
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. |
Finding duplicates
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. |
All times are GMT +1. The time now is 10:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com