Comparing Data

[RaDevi]

Hello

I've got two separate worksheets as given bellow, I need to be able to compare the two columns and get result in sheet3 as given bellow.


Sheet 1:
Colum 1
AA
BB
CC
DD
EE
FF
GG
HH
II
JJ


Sheet 2:
Colum 1
AA
CC
FF
GG
II


But i want

Sheet 3:
BB
DD
EE
HH
JJ

Can this be done?

 

[SkipVought]

Hi,

Paste both lists contiguously in the same column.

Assuming that this is all in column A, in column B...
[tt]
=countif(A:A,A1)
[/tt]
and copy down.

sort on column B

Delete rows with counts greater than 1


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

The solution depends on exactly what you want to do. There are two obvious things you might be trying to do:
(1) remove all items from sheet 1 which are also in sheet 2
(2) remove all items that are present on both sheets.

Skip's excelent solution solves problem 2. If you have things that are in sheet 2 but not sheet 1, they will also appear on sheet 3.

If you wish to solve exclusively problem 1, you could do almost the same. Just countif with your lists in place, rather than pasted contiguously. For simplicity, I put both lists on the same sheet, list 1 in column A, list 2 in column B. In this case use C1 = countif(B:B, A1), copy down. The copy everything, paste as value on sheet 3, sort by column C, and keep only rows with zero.