List A: All previous members (about 1300 names)
List B: All potential members (about 3000 strong, includes names from List A)
I’m sending out two different mailings, one to each list. However, I need to change List B into List C, which is “List B, minus people from List A”. The problem here is that whoever created List A didn’t see the need for a MiddleInitial column, and just tagged it onto the end of the FirstName. So would it be easiest to match these by LastName, then City, then Zip?
If this was your pile of information, how would you create List C?
I’m lazy. I’d append both lists, sort them by lastname, firstname, then write a formula in the adjacent column to flag (I usually use <==) anything where the lastname and the first like 3 characters of the firstname match, so something like
=IF(AND(A1=A2,LEFT(B1,3)=LEFT(B2,3)),"<==","")
and copy that all the way down. This example assumes lastnames in col A and firstnames in col B, natch.
Then I could just check the flagged rows and handle appropriately.
Remove duplicates will only catch those that are completely identical, not all the ones where you have “Foster, Stephen” next to “Foster, Stephen K.”
=if(ISNA(vlookup(<first List B cell>, <List A range>, 1, FALSE)), <first List B cell>, “”)
Then copy it down so you have one for each List B cell. For example, if List A is in A1:A1300 and List B is in B1:B3000, then in a cell on another sheet, type in:
Copy that down 3000 times. This will give you a list of either blanks or List B names. Use Autofilter to only show the blanks, then delete them (or, only show non-blanks, then copy what’s showing to yet another sheet), and Bob is your father’s brother.
If I’m following Ritter’s method, order of list B won’t matter, because it’s looking up each cell individually. So if list B starts at A1, <first List B cell> would be A1.
Order won’t matter, because the vlookup function looks at each item in List B and compares it to all the items in List A. I am assuming that the whole name is in one cell, though. That is, List A looks like:
Smith, John
Blow, Joe
And List B looks like:
Smith, John
Doe, Jane
Public, John Q.
where each of those items is in one cell each. If not, you would have to put together the names into a single cell. So, if List A has two columns:
Last Name First Name
Smith John
then you’ll need another column with both put together:
[QUOTE=RitterSport]
I am assuming that the whole name is in one cell, though
[/QUOTE]
Nope, as I mentioned, it’s all over the place. Married?, Title, FirstName, LastName, Suffix, Addresses, membership info, etc.
I would combine whatever the minimum needed to ensure a unique match. LastName&FirstName&Address or something like that. As long as you combine it the same way with List A and List B, it should work out. So, at the right of List A, have a column of formulas that concatenate the relevant fields. At the right of List B, do the same. To the right of that column, put the vlookup formula in, autofilter, and delete the blanks from List B. Obviously, do all this on a backup copy of the data or something.
And, if you have any complex last names (i.e., Garcia Lopez, Jingleheimer-Schmidt), you might want to strip those characters out of the concatenation to ensure that Garcia Lopez doesn’t not match Garcia-Lopez.