Exception deleting

C

cj21

I have a dataset that includes a coulmn of product codes-aprox 10'000
entires e.g

Prod Code Value
01023456 12
01204895 19
02857867 34
05292453 24
90235905 65

There is other data in differnet columns, with entries relavent to the
product codes. As you can see in my example with the value.

I have anothe list of codes - aprox 250 (that i call exceptions) that
are included in the above table that i want eliminate. Is there a
formula to do this

e.g

Exceptions
01023456
05292453

Thankyou for your help

Chris
 
M

Morrigan

Try use a helper column

=MATCH(A1,list of exception,0)

If the function finds a match with the list of exception, will return a
number otherwise it will return #N/A.

Then use Autofilter, custom, does not equal "#N/A", highlight all the
rows(should be those you want to delete), press F5, special, visible
only, delete them.


Hope it helps.
 
C

cj21

sorry

I'm not really sure how to do this. Could you post an example.

Thankyou very much for your help


Chris
 
M

Morrigan

See attachment

To turn on autofilter:
Tools -> Filter -> Autofilter

Click on the helper column drop down list and select:
custom -> does not equal & #N/A

This will only show all rows where the value under helper column doe
not equal #N/A. These should be what you want to delete

Highlight all visible rows, press F5 and choose:
special -> visible cells only -> OK

Now delete these row

+-------------------------------------------------------------------
|Filename: Sample.zip
|Download: http://www.excelforum.com/attachment.php?postid=4270
+-------------------------------------------------------------------
 
C

cj21

Thankyou this is very helpfull.

I have followed your procedure.

Could you amend it though. Instead of deleting the exceptions i would
like them to be cut and put elsewhere. Is this possibile?

Thankyou for your help

Chris
 
Top