The formulas that come out blank are for the records you can delete.


HTH,
JP


On Apr 7, 7:58*pm, RunOrDie
wrote:
I think I understand - but I'm looking to keep only the duplicates.



List-A has address in just one cell A1:

100 Main Street

List-B has address/city/state/zip and other data in separate cells (A6:D9)

I want to be able to keep only those in List B that match List A
Thanks -dg
--
Best wishes - DG



"JP" wrote:
Here's an example that might work for you.

List-A has address/city/state/zip in cell A1:

100 Main Street, Lenexa KS 67215

List-B has address/city/state/zip in separate cells (A6:D6)

This formula will check the full-address cell for the street address
and let you know if it's a duplicate. Adjust cell references as
appropriate.

=IF(NOT(ISBLANK(A6)),IF(SUM(LEN(A1:A2)-
LEN(SUBSTITUTE(LOWER(A1:A2),LOWER(A6),"")))=LEN(A6 ),"Duplicate",""),"")

This is one formula and should be entered as an array (Ctrl-Shift-
Enter).