please help an excel numpty

i have a spreadsheet

in the spreadsheet there are just two columns, both have email addresses

i want to identify the times where an email address is in both columns, and then when it is in both columns, i want that email address to be deleted, and i just want to be left with the addresses not in both columns


Remove duplicates

doesnt work

Ok well you’ve beaten me, congratulations.


That won’t work because it will leave a single version of all the duplicates you had and you want them both gone, no?

fuck sake


Put this in a third column =IF(A11=B11,1,0)

then sort on that column and delete all the 1s

doesn’t work

most likely because i’ve no idea how to do anything in excel


can you show this bit

i’m really thick, i’m so sorry

I searched on “excel compare two columns and delete matches”

Does this help?

You then need to sort columns a to c all together on C and then delete everything where C is a 1.

I’ve got a pretty faffy solution using tables and VLOOKUP if you’re still working on this:

Assuming you’re set up like @Balonz posted above:

  1. Select all of column two and hit “Format As Table”
  2. in cell C1, enter the following: =IF((ISNA(VLOOKUP(A1,Table1,1,FALSE))),"Unique","Duplicate")
  3. Drag the formula in C1 down to match the length of your list in column A
  4. Sort the lot alphabetically on column B and delete all the rows that say “Duplicate” in column B

It’s not neat, but it works…

Can they be in different rows and different columns?

If so I would do =countif(a2:b10,a2)+countif(a2:b10,b2)

Then delete any row with a value more than 1

(This assumes the emails are in the cells a2 to b10)

Ahh, I assumed they were next to each other…

May well be

they’re not next to each other, if an identical email appears they may well be in different rows

1 Like