batch delete

[Guest]

I have a file of about 10,000 lines that I down load each week. this file
contains SKU numbers I have a list of about 1,000 sku numbers in another file
that have to be deleted out of that big file each week before the big file
can be used. is there a way to run a batch delete using the list of 1,000 as
a guide of the sku numbers to be deleted from the 10,000 line file?

 

[Guest]

Here's a way using non-array formulas which can deliver the required extract ..
(do hang around awhile for possible vba options from others)

A sample construct is available at:
http://cjoint.com/?jve1fKzycl
Extracting an exclusion list.xls
(Link above is good for 14 days)

In a sheet: X,
the 1,000 reference sku's to be deleted are listed in A2 down (text label in
A1: sku)

In a sheet: Y,
the 10,000 lines downloaded source listing is in cols A to C, labels in
A1:C1, data from row2 down, where col A = sku (the key col)

Then in a new sheet: Z,
with the same col labels in Y pasted in B11

Place in A2:
=IF(Y!A2="","",IF(ISNUMBER(MATCH(Y!A2,X!A:A,0)),"",ROW()))
(Leave A1 blank)

Place in B2:
=IF(ROW(A1)>COUNT($A:$A),"",INDEX(Y!A:A,MATCH(SMALL($A:$A,ROW(A1)),$A:$A,0)))
Copy B2 to D2

Then just select A22, copy down to cover the max expected extent of data
in Y, say down to D10100?. Cols B to D will return the required results, ie
only the lines from Y with sku's not found in the list in X (the deletion
list), with all lines neatly bunched at the top.