Convert an Excel table into customised rows * columns

Question

I would like to do something similar as this thread, but got #REF! error. I want to convert it into customised rows * columns, e.g. convert the 3 * 4 table into 6 * 2 format. How to achieve this?

Answer 1

The following approach uses the very flexible Let function, which, however, is only available in Excel 365 and Excel 2021:

=LET(Matrix,$A$1:$D$3,rowsMatrix,ROWS(Matrix),colsMatrix,COLUMNS(Matrix),
cellValues,SEQUENCE((rowsMatrix*colsMatrix)/2,2,0),
INDEX(Matrix,INT(cellValues/colsMatrix)+1,MOD(cellValues,colsMatrix)+1))

---

EDIT: Additional Question in the Comments

In the approach described above, the matrix is distributed row-by-row among the columns, i.e., the first row starts with 1, 2, and so on.

To distribute the matrix by columns, i.e., column 1 has values 1-6 and column 2 has values 7-12, the SEQUENCE statement must be adjusted accordingly. In particular, the SEQUENCE statement controls the distribution mechanism, and you want to get a SEQUENCE that moves vertically rather than horizontally. Therefore, simply replace the SEQUENCE statement in the LET function with the following:

TRANSPOSE(SEQUENCE(2,(rowsMatrix*colsMatrix)/2,0))

If you want to understand to what extent the two SEQUENCES differ, simply enter the following formulas in Excel:

=SEQUENCE((3*4)/2,2,0)
=TRANSPOSE(SEQUENCE(2,(3*4)/2,0))

---

Another tangible approach would be to transform the data into a single column via (where, e.g., $F$1 is the top cell of the target range):

Option 1:

=OFFSET($A$1,INT((ROW()-ROW($F$1))/COLUMNS($A$1:$D$1)),
MOD(ROW()-ROW($F$1),COLUMNS($A$1:$D$1)))

Option 2:

=INDEX($A$1:$D$3,INT((ROW()-ROW($F$1))/COLUMNS($A$1:$D$1))+1,
MOD(ROW()-ROW($F$1),COLUMNS($A$1:$D$1))+1)

Thus, both options 1 and 2 are simply helper columns to which you can then apply the function below. Since the function also works if you enter the entire column, this approach seems quite flexible as well:

=INDEX($F:$F,SEQUENCE(6,2))