google sheets - Transform an array with offset and arrayformula

Question

I'm getting values from a google form, on one row, with an alternance of two types of data, 5 times. Like that : TypeA|TypeB|TypeA|TypeB|TypeA|TypeB|TypeA|TypeB|TypeA|TypeB

I want to transform that in an array with two columns, on another sheet:

TypeA|TypeB
TypeA|TypeB
TypeA|TypeB
TypeA|TypeB

I managed to do it with that formula: =offset(DataSheet!$B$2,FLOOR((row()-2)/5),mod(row()-2,5)*2)

I'd like to put that in an arrayformula, to avoid dragging the formula every time I have a form submission. I tried : =arrayformula(offset(DataSheet!$B$2,FLOOR((row(B2:B)-2)/5),mod(row(B2:B)-2,5)*2)) That works for the first line, but doesn't expand to the next line as expected/wished.

What am I missing?

Here's an example sheet: https://docs.google.com/spreadsheets/d/1nyN-V0ZjBsRU7-7I97lnLUQCsLpO8f27UlXtgoLa1j8?usp=sharing

question from:https://stackoverflow.com/questions/65602632/transform-an-array-with-offset-and-arrayformula

Answer 1

you can do it like:

=QUERY({DataSheet!B2:C; DataSheet!D2:E; DataSheet!F2:G; DataSheet!H2:I; DataSheet!J2:K},
 "where Col1 is not null", 0)

---

or like this:

={FLATTEN(FILTER(DataSheet!B2:K, MOD(COLUMN(DataSheet!B:K), 2)=0)),
  FLATTEN(FILTER(DataSheet!B2:K, MOD(COLUMN(DataSheet!B:K)-1, 2)=0))}