Is there a way to use an "exclude" list to clean up another list?

[mattt8]

Hello.

I have tried researching this, but am having no luck. I am using Microsoft Excel 2003 SP2, and we have a large mailing list purchased through Salesgenie. Unfortunately, some (or all) of our existing customers are also on that list, and there are certainly far too many to attempt to go through and remove them by hand. We want to "clean" the list so that it doesn't have any of our existing customers in it.

Is there a way to use a separate list of our existing customers and to "Anti-Merge" the two lists, which is to say, delete all duplicates so that the resulting new list is effectively LIST_C EQUALS LIST_A MINUS LIST_B .

Any ideas would be much appreciated!

Thanks
Matt

 

[Joe4]

Welcome to the Board!

I would recommend comparing the two lists using the function VLOOKUP. It will return an error if it doesn't find an entry from one list in the other (or will return whatever field you tell it to if there is a match). Take a look at Excel's built-in help on the VLOOKUP function for explanations and examples.

Note, however, that matching may not be 100% accurate if the fields you are comparing aren't exactly the same (for example if a company is listed as "ABC Inc." on one list, and is listed as "ABC Incorporated" on the other, a match will not be returned because they aren't exactly the same). This is a problem that is very difficult to get around. There have been some posts in the past regarding a user defined function someone created called "fuzzy lookup". You can search this board for that if you need to use something like that.

 

[btadams]

Here's a macro that will do it, assuming List A is in column A and List B is in column B AND there is only one possible duplicate name in list A:

Sub CleanList()
Dim rngListA As Range, rngListB As Range, cell As Range

    
    Set rngListB = Range([B1], [B65536].End(xlUp))
    
    For Each cell In rngListB
        Set rngListA = Range([A1], [A65536].End(xlUp))
        On Error GoTo SkipIt
        rngListA.Find(What:=cell.Value, After:=rngListA.Cells(1, 1), LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Delete shift:=xlUp
SkipIt:
        Set rngListA = Nothing
    Next cell
    Set rngListB = Nothing

End Sub

 

[bdfagoodfella]

If you are able to dump the data from both tables into the same worksheet you could then, in just a few steps, create a pivot table that would show all the names. This should also make it easy to spot the "not quite duplicates" which you are bound to have. (ABC inc. vs ABC incorporated.)

 

[mattt8]

Thanks very much to both of you.

I think some version of one or both of your ideas should work, with some tweaking...the macro seems awesome, but there's all kinds of data in the other columns other than a name (i.e., address, city, state, zip), so I could clearly see how I could nuke the names with that method, then maybe figure out a secondary way to delete all the other associated fields (maybe even if that part was manual). Also I'll have to try the VLookup function...I do understand that it can't/won't find non-exact matches, so I MAY try to find the "fuzzy" version, but I might just be OK with letting a few slip by here and there.

If there are any alternate suggestions, awesome, otherwise, I'll try these two and see what I come up with.

Thanks again!
Matt