[excel] compare 2 columns, delete duplicates

Random

i've got 2 columns. both contain a random string of characters and numbers mixed together.

i'd like to delete any duplucates. if any cell in a equals any cell in b i want to delete them and vice versa.

can anyone help me out please?

thanks

Victor

Is this a one off or do you need to do it constantly?

Does it only matter if the matching data is in the same row?

a b
1354 3274
5354 5434
6543 1554
4654 4654
5432 1354

1354 is common to 1a and 5b

4654 is common to 4a and 4b

---

Potentially, put this is cell C1

=if(a1=b1,"",a1)

Put this is cell D1

=if(a1=b1,"",b1)

You then have two columns with the unique data. If you want the duplicates:

=if(a1=b1,a1,"")

"" returns a null result.

Random

it's a one off. there's about 3000 rows. i can't just compare a3 and b3 and then a4 with b4 etc. i need to compare everything.

i've got this formula
=IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1)
but it still doesn't seem to do the job exactly as i hoped.

Victor

One a new worksheet, copy the data. Colour one column red, the other green, copy column B into A. Sort alpha-numerically.

Then insert in B2

=if(a1=a2,"Match","")

Alternatively assign codes for column a & b and do a more complicate IF - only worthwhile if there are lots of matches.

Random

maybe i'll go into more detail because maybe the easy solution won't work. then maybe it will?

i have 2 sheets. both sheets have 4 columns on them. it's basically pre and post stock take. i need to see where the discrep are.

i've created a 5th column on each spreadsheet and basically made it "=a1&b1&c1&d1". i'm then comparing both of the 5th columns against each other.

i'll take a look at your idea here though.

Victor

Surely they all will have some discrepancies, you just need to highlight the ones that are worth X amount.

Random

ill battle through it when ive a fresh head tomorrow. thanks for the posts so far anyway.

Gordon

Random wrote: »

i've got 2 columns. both contain a random string of characters and numbers mixed together.

i'd like to delete any duplucates. if any cell in a equals any cell in b i want to delete them and vice versa.

can anyone help me out please?

thanks

You can probably do something like this:
=IF((COUNTIF(A:A,A1)>1),"","A1")

If I've got that right it means - count if there are more than one existance of A1 in column A, if so output "", if not output A1. Copy that formula into columns B&C and use B and C as your output.

Actually, scratch that, that's giving you Ax matching column A. Use that as your starting point so!

mid

Random wrote: »

i've got 2 columns. both contain a random string of characters and numbers mixed together.

i'd like to delete any duplucates. if any cell in a equals any cell in b i want to delete them and vice versa.

can anyone help me out please?

thanks

Not certain if this is what you want, but see the attached file.

The first sheet compares the 2 columns. I then copied and pasted the results to a new sheet and done autofilter by '#N/A', which hides the duplicates. As its a once off thing you need to do this might be ok