please help an excel numpty

Social
#1

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

HOW PLZ HELP

#2

Remove duplicates

#3

doesnt work

#4

Ok well you’ve beaten me, congratulations.

#5

NO IGNORE ME I STILL NEED HELP

#6

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

#7

fuck sake

yes

#8

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

then sort on that column and delete all the 1s

#9

doesn’t work

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

#10

image

#11

can you show this bit

i’m really thick, i’m so sorry

#12

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

https://www.extendoffice.com/documents/excel/3250-excel-compare-two-columns-and-delete-matches.html

Does this help?

#13

image
image740Ă—173 4.25 KB

#14

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

#15

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…

#16

Can they be in different rows and different columns?

#17

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)

#18

Ahh, I assumed they were next to each other…

#19

May well be

#20

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

1 Like