0

In Google Sheets, I have a set of data in column A1 to A100. In column B, I would like to reference three rows from column A while skipping every other two rows, like this:

Column A
1
2
3
4
5
6
...
100

Column B
3
4
5
8
9
10
...

How do I do that? I can use the offset function to skip every nth row, but I can only make it display one row at a time, when I actually want three rows at a time.

1

1 Answer 1

1
+100

You apparently want to skip rows 1,2, 6,7, 11,12, 16,17, 21,22 and so on. Chances are that the data in these rows differs somehow from the data in the rest of the rows, and the easiest solution would probably be to filter by that data rather than blindly filtering by row numbers.

Nevertheless, here is a formula that filters by row numbers the way you describe:

=filter(A1:A, regexmatch(trim(mod(row(A1:A), 5)), "3|4|0"))

See filter() and regexmatch(). To learn the exact regular expression syntax used by Google Sheets, see RE2.

Here are a couple additional patterns to choose which rows to show.

Only show rows 5, 10, 15 and 77:

=filter(A1:A, regexmatch(trim(row(A1:A)), "^(5|10|15|77)$"))

Show all rows except rows 5, 10, 15 and 77:

=filter(A1:A, not(regexmatch(trim(row(A1:A)), "^(5|10|15|77)$")))

Only show rows with odd row numbers 1, 3, 5, 7...:

=filter(A1:A, isodd(row(A1:A)))

Only show rows with even row numbers 2, 4, 6, 8...:

=filter(A1:A, iseven(row(A1:A)))

Show every third row starting from row 2:

=filter(A1:A, (row(A1:A) - 2) / 3 = int((row(A1:A) - 2) / 3))

Show rows 4,5, 9,10, 14,15, 19,20, 24,25...:

=filter(A1:A, 
  ((row(A1:A) - 4) / 5 = int((row(A1:A) - 4) / 5)) 
  + 
  ((row(A1:A) - 5) / 5 = int((row(A1:A) - 5) / 5)) 
)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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