Autofilter for not equal to

parny

New Member
Joined
Apr 8, 2016
Messages
4
Hello All,

I have a range of data in sheet 1 that I want to filter by column one. Column one contains cells like "BCX" and "DUV". I want to filter out rows that in column 1, start with the letters: B, X, N, C, V, M, L, H, and D. I then want to copy the remaining values to a new sheet.
I have seen somewhere that you can only have three criteria when auto filtering, in that case I may have to filter three times.

I know how to filter out one but I can't filter out more than one.

If anyone can help me with this dilemma that would be great.

Thanks
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi, have you considered using a helper column? For example you could put this formula a spare column and filter on that where it equals TRUE.

=OR(LEFT(A2)={"B","X","N","C","V","M","L","H","D"})
 
Upvote 0
Hi, have you considered using a helper column? For example you could put this formula a spare column and filter on that where it equals TRUE.

=OR(LEFT(A2)={"B","X","N","C","V","M","L","H","D"})

Hello FormR,

Yes I have tried this but it doesn't seem to work for criteria not equal to: "<>B*" etc.
 
Upvote 0
unfortunately I need the whole filter to be automated as soon as I copy in the data.

Sorry, I don't follow - this is the first time you have mentioned automation.
 
Last edited:
Upvote 0
Sorry, I don't follow - this is the first time you have mentioned automation.

Sorry I should have been more clear. I have a set of data that want to be able to copy into sheet 1 of a workbook and then it automatically filters out all models (in column A) to the pre-set sheets (being the letters mentioned earlier). I have been able to do this for all of the letters except from the 'others sheet'. This sheet requires the rest of the models not copied to the other sheets already (so ones not beginning with the listed letters). I have no idea how to do this without listing all of the possible models it could be and filtering for that (this is a long list so I don't really want to do that). Ideally I would like to be able to state: copy sheet 1 to 'others sheet' and take out rows with column A beginning with the mentioned letters.
 
Upvote 0
Can't you programmatically enter the formula in post#2 into the first un-used column and filter on that column where it =FALSE; do your copy and paste and then remove the formulated column?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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
Back
Top