Autofilter problem

terryc

Board Regular
Joined
Jun 27, 2006
Messages
66
I have a list of data that I want to filter using the Autofilter command.

One column contains numbers from 1-96. I want to filter out all numbers apart from those beginning with 1 and then moving up in increments of 4, i.e. 1, 5, 9, 13, 17, 21, etc.

If I go to the custom autofilter, I can put "equals" 1 AND "equals" 5, but obviously I can't go any further as I can only put 2 conditions in.

Is there any way of doing this? Something along the lines of:
"equals" 1 OR 5 OR 9 OR 13 OR 17 OR 21 ......etc.?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
In a new column, use =MOD(A2-1,4)=0 and copy down. Filter this new column for TRUE to show your 1,5,9,13,17, etc. values or FALSE to show everything else.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi terryc

Another way is to use Advanced Filter instead of Autofilter and use Peter's formula as criterion.

Hope this helps
PGC
 

Watch MrExcel Video

Forum statistics

Threads
1,113,795
Messages
5,544,332
Members
410,603
Latest member
rseckler
Top