looking to delete "non-matched" data
 

I have two lists done in excel.

An older one, List-A, with separate columns for name, street, city, email
addresses, company name, etc. and a newer one, List-B, that has just one
column of current email addresses that will match some of those in List-A.

I know I can copy the data from List-B into List-A and I'll end up with
duplicate emails of those I want to keep.

How can I delete the ones from List-A that have no match from List-B?

I looking for the opposite of getting rid of the duplicates - :)
Best wishes - DG

looking to delete "non-matched" data
 

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

I put a sample workbook here if you want to check it out:
http://www.codeforexcelandoutlook.com/FindDupes.xls


HTH,
JP


On Apr 7, 6:46*pm, RunOrDie
wrote:
I have two lists done in excel.

An older one, List-A, with separate columns for name, street, city, email
addresses, company name, etc. and a newer one, List-B, that has just one
column of current email addresses that will match some of those in List-A.

I know I can copy the data from List-B into List-A and I'll end up with
duplicate emails of those I want to keep.

How can I delete the ones from List-A that have no match from List-B?

I looking for the opposite of getting rid of the duplicates - :)
Best wishes - DG

looking to delete "non-matched" data
 

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

I put a sample workbook here if you want to check it out:
http://www.codeforexcelandoutlook.com/FindDupes.xls


HTH,
JP


On Apr 7, 6:46 pm, RunOrDie
wrote:
I have two lists done in excel.

An older one, List-A, with separate columns for name, street, city, email
addresses, company name, etc. and a newer one, List-B, that has just one
column of current email addresses that will match some of those in List-A.

I know I can copy the data from List-B into List-A and I'll end up with
duplicate emails of those I want to keep.

How can I delete the ones from List-A that have no match from List-B?

I looking for the opposite of getting rid of the duplicates - :)
Best wishes - DG

looking to delete "non-matched" data
 

Try this on a spare copy of your file

Assume your sheet: List-B contains the emails in A2 down

In sheet: List-A,
Assume your source data is in cols A to F, from row2 down,
with the key "Email" col in col D

Put this in say, K2:
=IF(D2="","",IF(ISNUMBER(MATCH(D2,'List-B'!A:A,0)),"In List-B","Not in
List-B"))
Copy K2 down to the last row of data in col D. Col K will provide the flags
for you to easily autofilter according to what you want to do. The flags will
be:

In List-B : where the email in col D is found in List-B's col A
Not in List-B : where the email in col D is not found in List-B's col A
Blank: "", where col D is blank

Then you could apply autofilter on col K
and choose this from the dropdown in K1: Not in List-B
if you want to do this:
How can I delete the ones from List-A that have no match from List-B?
Then just select the filtered rows, right-click Delete Row, then remove
autofilter, and you should be left with the required results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

looking to delete "non-matched" data
 

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