How can i create a new column that inserts cells from other columns, but in a pattern of 3 from firs-CodePudding

So In this exmple I have 3 columns with 9 cells each. (Its only an example, in real life it may be 100 columns with 1000 cells each).

Columns:

Apples
Bananas
Grapes

I want to create a 4th column that has the first 3 cells from apples, then 3 cells from bananas, then 3 cells from Grapes, then 3 cells from Apples again, and on...

So I tried giving the cells a secuence, and then dragging the secuence downwords hoping excel would get the pattern I was looking for. Then I thought of copying all 3 columns in one column and ordering the secuence from most small to most big, but Excel dosent get my pattern.

Do you guys know any other possible solution? Or how can I get Excel to get my pattern when dragging down?

CodePudding user response：

=LET(a,A3:A11,
     d,D3:D11,
     h,H3:H11,
     join,HSTACK(a,d,h),
     rw,ROWS(join),
     col,COLUMNS(join),
     sq,SEQUENCE(rw*col,,0),
INDEX(join,
      (MOD(sq,col) 1) col*INT(sq/rw),
      MOD(INT((sq col)/col)-1,col) 1))

It stacks the ranges and indexes it. The sequences reference the correct rows/columns.

If you add a range the formula will still work, as long as you add it to join

CodePudding user response：

Assuming the total number of rows of the input is divisible by 3, then you can try in cell H2:

=DROP(REDUCE("", SEQUENCE(ROWS(A2:A10)/3,,0,3), LAMBDA(ac,cnt, LET(ss, 
  SEQUENCE(3) cnt, VSTACK(ac, CHOOSEROWS(A2:B10,ss), CHOOSEROWS(C2:D10,ss), 
  CHOOSEROWS(E2:F10,ss))))),1)

Here is the output:

Explanation

We use REDUCE to concatenate the VSTACK output per iteration. The number of iterations is defined by ROWS(A2:A10)/3 and the rows to select on each iteration via CHOOSEROWS is determined by: SEQUENCE(3) cnt.

The array:

SEQUENCE(ROWS(A2:A10)/3,,0,3)

represents the offset we apply on every iteration: 0,3,6,....

DROP is used to remove the first row, because we don't have a good value to initialize the accumulator (ac). You can initialize with the header, in case you want to generate it, then DROP function is not required. For example replace REDUCE("" with: REDUCE({"Cells", "Ordered Sequence"}

If you don't want to generate the Seq column, then adjust the range in CHOOSEROWS or just use INDEX instead.