Highlight duplicates in 2 columns: Keep duplicates, delete rest

[tobyvb]

Hi there,

I am new to the forum since today because I am facing a problem highlighting duplicates in a large Excel file (at around 200.000 rows).


Situation:
Our suppliers sent a list with over 200000 rows in a file. I only need 1800 of them. I can identify the 1800 needed rows by the SKU number, which is the same in both documents.

For smaller files I would do the following:

1. Paste the needed SKU numbers in a new column in the file from the supplier
2. Use conditional formatting to highlight duplicates in those two columns
3. Create a filter for the existing column and filter on the colour I used to highlight the the numbers
4. Copy the highlighted rows to another document

End goal: I have one file with all supplier information which I need.


Problem:
Can't apply the steps written above since the file of the supplier is too big. Once I want to filter the rows on highlighted colour, whole excel crashes.



Question:
Is there any way I can filter those 1800 articles from the 200000 rows in the large file without crashing Excel?



Thanks in advance.

 

[EFANYoutube]

So there is probably some fancy pants way of doing this but looks like you just need a one off solution
I put the SKUs in "Articles" sheet in column A
I put the SKUs to look for in sheet "SKUsToKeep" in column A also
Then I copied down the word "Keep" beside all the SKUs to keep in column B
Then the following formula worked

=IFERROR(VLOOKUP(Articles!A1,SKUsToKeep!A:B,2,0),"Delete")