Reshaping a matrix with formulas

Hi, I sort of understand how I transpose a matrix of the form n × m into m × n form
and also how to reshape a matrix from the form n × m into p × q as long as n/q (respectively m*q) are integers.

e.g. how to transform an 8 × 6 matrix into a 4 × 12 matrix ( 8 / 4 = 12 / 6 = 2).
Whether the transformation is done row-wise or column-wise is not really important, so in my attached examples I went for row-wise, i.e. row by row.

I now wish to do the same where the above condition is no longer satisifed, e.g. transforming a 5 × 4 matrix into a 2 x 10 matrix, i.e. 5 / 2 or 10 /4 = 2.5 (or more generalised, into any form, e.g. also a 7 × 3 matrix, acknowledging that the last data point cannot be populated, i.e. would be a zero/REF#! depending on whether this is done via OFFSET or INDEX).

Attached examples.
The first matrix is reshaped as explained above, so there're no problem there.
Below that I try the more general approach, which fails starting at row 2: The first value to be picked up there should obviously be 70 and not (again) 6.

Can somebody support me in changing the formulas accordingly?
It seems that both the working of the row AND column determination need to be changed.

I prefer to avoid OFFSET as it is volatile, so I'll try to work out a solution using INDEX with MOD and INT to generate the appropriate rows and columns.

Pete

Originally Posted by Pete_UK

I prefer to avoid OFFSET as it is volatile, so I'll try to work out a solution using INDEX with MOD and INT to generate the appropriate rows and columns.

Pete

Attached as INDEX version, maybe you prefer working with that one:
At least the way the formulas are set up here, there's only 2 minimal adjustments needed to get to the same result via OFFSET or INDEX:
- the starting point is a (fixed) range, rather than a cell
- the row and columns shifts are increased each by 1 compared to the OFFSET alternative (basically because INDEX 1st value correspondenx to OFFSET shift by zero cells - hope you can follow me)

Or a bit more illustratively:

=INDEX($B$4:$G$11,INT((ROW()-ROW($B$21))*$G$19+((COLUMN()-COLUMN($B$21))/$D$15))+1,MOD((COLUMN()-COLUMN($B$21)+$H$15*(ROW()-ROW($B$21))),$D$15)+1)
equals
=OFFSET($B$4,INT((ROW()-ROW($B$21))*$G$19+((COLUMN()-COLUMN($B$21))/$D$15)),MOD((COLUMN()-COLUMN($B$21)+$H$15*(ROW()-ROW($B$21))),$D$15))

I've managed so far to amend the formulas so these seem to work at the moment on both data sets, but it's a work in progress style, so I'm right in the middle trying to figure out how stable the formulas are.
So no guarantees on nothing :D

How about

Formula:

Please Login or Register  to view this content.

Originally Posted by Fluff13

How about

Formula:

Please Login or Register  to view this content.

Never worked with filterxml; where do I start trying to understand this? xD

Never worked with filterxml; where do I start trying to understand this?

It basically just turns the text string into a vertical array

@Bo_Ry: On this version: =LET(z,B30:E34,y,z+ROW(z)%+COLUMN(z)%%,INT(INDEX(MODE.MULT(y,y),SEQUENCE(H38,H36))))
What's the purpose of the % and also of the double %%? In the latter one, it doesn't seem to make a difference if one is deleted or if I switch rows and columns on the % and %%?

@Bo_Ry / @Fluff: On your solutions, how would these formulas look instead if the matrices were to be reshaped columnwise?

That is, first row would read: 19 / 100 / 6 / 24 / 90 / 18 / 23 etc.?

Thx for your help so far. Much appreciated.

For my suggestion, you can use transpose, like Bo_Ry did

Formula:

Please Login or Register  to view this content.

Although Bo_Ry's is much simpler than mine.

Thanks a lot Pete, very instructive.
That's a lot of effort you've put into doing this write-up!

I will go through again later a bit more in detail.

Just a comment from personal taste, pls don't take any offence: In terms of counters I personally prefer something like e.g. in A1: ROW()-ROW($A$1), which btw doesn't create a circular reference even though a cell is referring to itself, to something like this: ROWS($1:1).
The reason is mainly file auditing: Referring to the subject cell itself will never create the issue that accidentally deleting another cell will compromise the formula by creating a REF#! error because that deleted (often blank) cell was used elsewhere for counting.
I consider referring to entire columns or rows 'bad style' because effectively every cell in that column is referring to the formula cell: for each cells that's 6x over 16,000 cells and 3x over 1 million cells.
Fortunately, such kind of references, as far I understand, don't result in slower calculation times. It's just counterintuitive why e.g. cell N1048572 is needed for a a bunch of calculations in rows 21 to 24.
A further downside is that in Office 365 these types of formulas unnecessarily try to spill, so for me the formulas start like this: =@IF and this: =@INDEX, i.e. preventing the spilling behaviour.
I try to avoid spilling where it's not needed.

Anyway, cheers.