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
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,220
Office Version
365
Platform
Windows
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"})
 

parny

New Member
Joined
Apr 8, 2016
Messages
4
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.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,220
Office Version
365
Platform
Windows
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 have tried this but it doesn't seem to work for criteria not equal to: "<>B*" etc.
Hi, couldn't you just filter for FALSE rather than change the formula.
 

parny

New Member
Joined
Apr 8, 2016
Messages
4
Hi, couldn't you just filter for FALSE rather than change the formula.
unfortunately I need the whole filter to be automated as soon as I copy in the data.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,220
Office Version
365
Platform
Windows
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:

parny

New Member
Joined
Apr 8, 2016
Messages
4
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.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,220
Office Version
365
Platform
Windows
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:

Forum statistics

Threads
1,082,603
Messages
5,366,578
Members
400,904
Latest member
ndaines meriabi

Some videos you may like

This Week's Hot Topics

Top