Formula for Comparing Two Columns of Emails

Hi,

I know I have seen this formula before, but I cannot for the life of me find it or remember it and hoping someone here can help. I will explain what I need the best I can.

I have column A with emails that I have listed as "Bad" or "don't send" and I have pasted in Column B a new list of emails that I want to send out, but I need to make sure none of the emails in column A are in the list of Column B. so what I am looking for is for Column B, new emails, be compared against master email list in Column A and put net results in Column C. That way I can then use those emails and not worry that I would send another email to someone that has unsubscribed.

Column A = Master Unsubscribed List of Emails

Column B = New email list I want to send out

Column C = Net new email list making sure no emails from column B are in Column A.

Thanks for your help!

That might be the trick. I am on a mac and it says to use the "key"combinations, not sure if they are the same on the mac, but I have tried several combinations but they don't paste anything there. I can paste normally but not using the key combinations.
If I have over a 1000 names, what is the simplest way to make this work for the entire column? Do I just need to scroll down once it is in the first column C cell?

Hmm, nothing. When I click on command return, it just moves to the next cell below without putting anything in.

I have my information starting in row 2 as I have a heading, so I would be starting in Column C row 2 for this formula. Am I doing something wrong? I am copying your formula and then hit command return in column C row 2.

Thank you. I appreciate your willingness to help. I know the commands should be about the same, but I guess not on this one.

Thank you for adding your insights, unfortunately, it is still not working. I have even searched as suggested. One question I did have, when you say "select the range" what cells am I supposed to be selecting? Column A, B and C or just A and B or just C? Sorry . . .

Okay, I have tried that with only the first cell giving me an email back. The rest of the cells show a green icon in the left but no emails.

I have included a basic file as my test file in hopes you can replicate what I need to do. If I paste the code: =IFERROR(INDEX(B:B,SMALL(IF(ISNA(MATCH(B$2:B$9,A$2:A$5,0)),ROW(B$2:B$9)),ROWS(C$2:C2))),"")

in cell c2, and then drag copy, is that the process? I am not getting anything yet.

Thanks in advance again for your help in this matter.

Okay, I have opened this on a windows machine using excel 2013. This is what I have tried and failed so far, still.

1. copy =IFERROR(INDEX(B:B,SMALL(IF(ISNA(MATCH(B$2:B$9,A$2:A$5,0)),ROW(B$2:B$9)),ROWS(C$2:C2))),"") to clipboard

2. click on the C2 cell and click CTRL+SHIFT+ENTER.

3. Nothing happens

2nd attempt.

1. Copy

2. Paste in C2 and then drag copy down. The first cell has an email in it, but the rest of the C cells don't show anything. If I click on a cell it will show me the formula but nothing shows up

What am I doing wrong? It feel like I am simply not clicking the right button or cell . . .

If you're using XL2010+, here's a formula that doesn't require array enter..

=IFERROR(INDEX($B$2:$B$9,AGGREGATE(15,6,1/(1/(ISNA(MATCH($B$2:$B$9,$A$2:$A$5,0))*(ROW($B$2:$B$9)-1))),ROWS(C$2:C2))),"")

Okay, it seems much better. Just two more questions and then I'll leave you all alone.

1. In Column A I have 850 rows. Column B I have 13000 rows. I am assuming in the formula I need to change the B$9 to B$13000 in three instances? What about Column A, do I need to adjust anything in the formula to accommodate the 850 rows?

2. Is there a quicker way of drag copy the formula to all 13000 rows? Or do I have to just drag to the bottom?

Thanks again

Answer of 1st question :
Change $A$2:$A$5 to $A$9:$A$850

Answer of 2nd question :
As I have no idea about MAC, I am not sure whether this will work or not there. You may try. Say you have entered the formula in C9, Then copy C9. Now Select C13000, then Ctrl+Shift+Up Arrow. This will select all the cells from C13000 to C2. Now paste the formula.

Thank you very much. What does the $A$9 represent? Meaning, how do know when to change it to what?

One more thing I wanted to bring up or ask, when I followed the instructions "Say you have entered the formula in C9, Then copy C9. Now Select C13000, then Ctrl+Shift+Up Arrow. This will select all the cells from C13000 to C2. Now paste the formula."

I did this, but most of the cells in column C paste in the very first cell I copied. Not in all, but 80% of them. It seems if I go really slow it takes, but if I try and do the entire column it fills in the column C cells with the first Cell from the formula. Any idea? Does it just take forever to do this? I have both a mac and windows so I am working on both and get the same result from either.

$A$9 represent your 1st cell of column A from where your data started and $A$850 is the last cell.

As you are using array formulas on 13000 rows so it will take some time to calculate. One more thing I made a typo on my 1st post. It will be "from C13000 to C9".

@Rack38,

If you are still having difficulties, you might find this approach is easier and quicker, as it doesn't use array formulae, but it does use column C as a helper column (coloured blue).

I've taken your file from Post #12 and put this formula in C2:

=IF(B2="","-",IF(COUNTIF(A:A,B2),"-",MAX(C$1:C1)+1))

This can be copied down to the bottom of the list that you have in column B, or even beyond - the hyphens will indicate where the formula is active. This sets up a unique list of sequential numbers for those addresses which do not appear in the list in column A. Then I used this formula in D2:

=IF(ROWS($1:1)>MAX(C:C),"",INDEX(B:B,MATCH(ROWS($1:1),C:C,0)))

and this can be copied down until you start to get blanks (I've copied to row 24 in the example file) to give you the list of acceptable email addresses.

No need to bother about how much data you have and adjusting the formulae to suit, and both of the formulae are normally-entered.

Hope this helps.

Pete