Find and Remove Duplicates from 2 Lists

[ellistyle]

I have a huge list (60k+ lines ) in one sheet. I have a smaller list on another sheet. Most of the short list items are the huge list. I know I can copy the short list lines over, sort and highlight duplicates. That seems clunky.


What is the best way to remove all of the short list items from the huge list?

 

[bobhc]

Good evening ellistyle you may find this link helpful


http://www.get-digital-help.com/2009/06/04/highlight-the-second-or-more-duplicates-in-two-lists-using-conditional-formatting-in-excel/

 

[kaushik03]

Hi ellistyle,


In case you want to remove the duplicates from two lists and get the unique list separately, then you can do the following:


Assuming you have the data as follows:


Col A Col B

List1 List2

A G

B A

C M

D R

E E

F B

E S

D

A

B


Note: List1 List2 are headers at A1 and B1, respectively.


Then at C2 write: =IFERROR(IFERROR(INDEX($A$2:$A$11,MATCH(0,COUNTIF($C$1:$C1,$A$2:$A$11),0)),INDEX($B$2:$B$8,MATCH(0,COUNTIF($C$1:$C1,$B$2:$B$8),0))),"") (press ctrl+shift+enter from keyboard to enter it as an array formula)....and copy it all the way down as long as you want.


At column C you will get unique list of data from column A and column B


Regards,

Kaushik

 

[SirJB7]

Hi, ellistyle!


Assuming your big list is in sheet Sheet1 at column A and that your short list is in sheet Sheet2 at column A, do this:

a) type in cell B1 of first sheet and copy down as required:

=SI(ESERROR(BUSCARV(A2;Sheet2!A:A;1;FALSO));"";"X") -----> in english: =IF(ISERROR(VLOOKUP(A2,Sheet2!A:A,1,FALSE)),"","X")

b) apply AutoFilter in first sheet to column B

c) filter "X" value

d) select filtered rows, right button on row number labels, Delete Row


Regards!

 

[Montrey]

Or you could use advanced filter which is located under sort.