Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks, is there anyway to handle it automatically ??? I can handle it with JAVA but I want to fix it with Excel if it possible –  Sam Jan 17 '12 at 15:30
1  
If you fill in the first three cells, then select all three cells. In the bottom right corner of the selection, there will be a little black square. Click and drag the square down, and it should automatically continue filling in the formula (and incrementing the offset for you). You can also double-click the little square to have Excel automatically determine how far to bring it (depending on the contents around it). –  Breakthrough Jan 17 '12 at 15:32
    
OK, Thank you very much indeed, I'll do it !!! –  Sam Jan 17 '12 at 15:35
1  
@MikeRedford also if that doesn't work, you can put the row offset in a different column, and then use Excel's auto-fill (sometimes it works kind of funky with formulas). So again let's say your data is in column A. In column B, put 4, 8, and 12, and then you should be able to auto-fill down as far as you need. Then in column C, first row put the formula =OFFSET($A$1, B1-1, 0, 1, 1). Finally, you should be able to auto-fill down this cell to get your desired data. –  Breakthrough Jan 17 '12 at 15:36
    
It works perfectly, thanks again & good luck buddy !!! –  Sam Jan 17 '12 at 15:49
add comment

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.

enter image description here

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.