Autofilter problem

[terryc]

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

 

[Peter_SSs]

terryc

One way would be to use a 'Helper' column. Formula in B2 (copied down):
=MOD(A2,4)=1
Then do the AutoFilter on column B = TRUE

Mr Excel.xls
	A	B	C	D
1	Number	Helper
2	1	TRUE
3	2	FALSE
4	3	FALSE
5	4	FALSE
6	5	TRUE
7	6	FALSE
8	7	FALSE
9	8	FALSE
10	9	TRUE
11	10	FALSE
12	11	FALSE
13	12	FALSE
14	13	TRUE
15	14	FALSE
16	15	FALSE
17	16	FALSE
18	17	TRUE
19	18	FALSE
AutoFilter

 

[Oaktree]

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]

Hi terryc

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

Hope this helps
PGC