find and highlight multiple cell duplicates quickly

[reggy]

hello,

i have spent an age searching for an answer for my question but i think my lack of excel terminology isnt helping me find the answer i need.

basically i have 2 files, one file is a product CSV file which i use daily to update prices on our website.

The second file is what we call a black list, this is products which are not available any more.

Once i have sorted the price and info i need from the main file (product) i need to remove the products which are on the black list file.
unfortunately i seem to be spending an hour at a time finding each and every duplicate SKU and deleting the row.

i am sure there must be a quicker way i can copy/paste my list of blacklisted SKu's and highlight on the main sheet so i can delete them quickly.

the black list gets larger every day so i am spending hours doing what took me 5 minutes originally.

any help would be appreciated!

regards
gareth

 

[GlennUK]

Why don't you have an extra column that simply checks to see whether a SKU exists in the blacklist ... then do an AutoFilter to show those, and then delete the lot in one go. Even typing the formula by hand, and doing the rest by hand this should only take a couple of minutes.

A typical formula might be:
=NOT(ISNA(A1,blacklist,0))

 

[reggy]

Hi Glenn,

Thanks for your reply but I'm afraid I still do not understand how to apply your suggested formula to my data... I'm am not normally this bad with Excel!

I have in column A all my data and in column D all my blacklist items that I would like these items to be flagged in someway in column A so that I can delete them quickly.

Thanks in advance for any help you are able to offer.

 

[GlennUK]

Put your blacklist in a different sheet. If you do deletion of rows in column A you could damage your blacklist.

Make sure you have a header in column A.

I have put my blacklist in sheet2. My data and formula would look like this:

Excel Workbook
	A	B
1	Data	Flag
2	Chicken	FALSE
3	Beef	TRUE
4	Beer	TRUE
5	Venison	FALSE
6	Crab	FALSE
7	Lobster	FALSE
8	Prawns	FALSE
9	Beans	TRUE
Sheet

Then doing Data Filter, on column B being TRUE shows all the records that are in the blacklist. Selecing those rows and doing Row Delete, gets rid of them all. Then reset the filter and the remaining records are those not on the blacklist.

 

[reggy]

I still cannot get this to work at the moment it just gives me 'TRUE' in every column - even using your data as a sample. I assume where you put blacklist I need to highlight the cells where my blacklist codes are listed eg. A1:A94 on sheet 2?
I'm really sorry to be such a pain...

 

[GlennUK]

Show a subset of your data, and show a subset of your blacklist.