Exception deleting

[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

[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.

[cj21]

sorry

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

Thankyou very much for your help


Chris

[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 does 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 rows

[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

[Morrigan]

Copy and paste before deleting?