Highlight duplicates in 2 columns: Keep duplicates, delete rest

tobyvb

New Member
Joined
Jun 23, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

EFANYoutube

Active Member
Joined
May 19, 2017
Messages
278
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
Excel Formula:
=IFERROR(VLOOKUP(Articles!A1,SKUsToKeep!A:B,2,0),"Delete")
 

Forum statistics

Threads
1,140,993
Messages
5,703,611
Members
421,305
Latest member
ambuj Thakur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top