how to filter each 4 rows in Excel?

Question

Actually I have an Excel file(xlsx - MS Office 2010) file that it has a column (Just A) and 3000 rows, I want to filter it each 4 rows, for example at first I want Row 4 after that row 8 after that row 12 and ...

for example :

TEMP -- 1
TEMP -- 2
TEMP -- 3
TEMP -- 4
TEMP -- 5
TEMP -- 6
TEMP -- 7
TEMP -- 8
TEMP -- 9
TEMP -- 10
TEMP -- 11
TEMP -- 12
TEMP -- 13

and I want this result :

TEMP -- 4
TEMP -- 8
TEMP -- 12

how can I fiter it please ? sorry for bad english ...

Answer 1

The easiest way is to use the OFFSET function. The function prototype appears as follows:

=OFFSET(range, rows, columns, height, width)

To apply this for your situation, set range to the first cell of data (use an ABSOLUTE reference, see below in the formulas), rows to the offset, columns to 0 (since we don't want to shift the column), and finally set height and width to 1 (since we are returning a single cell).

So, assuming your data above is in column A, you can return the 4th, 8th, and 12th entry via the following formulas:

=OFFSET($A$1,  3, 0, 1, 1)  or  =OFFSET($A$1,  4 - 1, 0, 1, 1) 
=OFFSET($A$1,  7, 0, 1, 1)  or  =OFFSET($A$1,  8 - 1, 0, 1, 1) 
=OFFSET($A$1, 11, 0, 1, 1)  or  =OFFSET($A$1, 12 - 1, 0, 1, 1) 

Note that you subtract 1 from each, since rows is an OFFSET (so to get cell A4, you offset A1 by 3 rows, not 4). If you enter the first few cells manually, you can use Excel's auto-fill function to perform this automatically for the rest of the data.

---

You can also use the INDIRECT formula to accomplish this. First, make a new column with the row numbers you want (e.g. 1, 5, 9...). Then, in the adjacent columns, put the formula =INDIRECT("A" & REF) where REF is the cell to the left (with the row number), and "A" is the column containing your data.

Obviously, you can do this for any arbitrary number set. So, if you enter the following into Excel:

    A    B          C
1   D1   4   =INDIRECT("A" & B1)
2   D2   8   =INDIRECT("A" & B2)
3   D3  12   =INDIRECT("A" & B3)
4   D4
5   D5
6   D6
7   D7
8   D8
9   D9
10 D10
11 D11
12 D12

You will see the contents D4, D8, and D12 in column C. You can use auto-fill to extend these formulas as far as you need.

Answer 2

The "modulus" operator using the ROW() operator would let you filter quickly. In a 2nd column, add the formula shown below. Note that I have put your data beginning in the 2nd row. Then all you need to do is filter column B on the "0" values.