Autofilter for not equal to

[parny]

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]

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]

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]

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]

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]

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.

 

[parny]

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]

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?