I am after a way to pull a consistent group of rows with an equal spacing between each.

Example: My data range is in A3:A250. I'd like to pull A3:A10,A15:A22,A27:A34,A39:A46, etc. through to the end of the dataset at A250. So, I am pulling 7 rows each time, with a consistent gap of 5 cells between each set of pulled row sets.

Requirements: I need this formula to all sit within one cell at the top of the data, and spill down to the whole data range, this is because the data will vary in length.

I am aware of the =OFFSET functionality, and can use =OFFSET(A:A,(ROW()-1),0,7) which will pull the first 7 cells, although (unless I'm mistaken) I'd need to fill this formula down for it to pull the whole range. Is there a way for me to put this in once at the top (in B1 for example) and have it fill for the whole range?

I am using Excel for MSO 365.

Thanks!

CodePudding user response：

You can try:

=FILTER(A3:A250,MOD(SEQUENCE(248,,0),12)<7)

Or, using the newest insider's BETA-channel functionality:

=TOCOL(TAKE(WRAPCOLS(A3:A250,12),7),,1)

Or, even less verbose:

=TOCOL(TAKE(WRAPROWS(A3:A250,12),,7))