Rotate multiple data from many rows and combine them into one column-CodePudding

I have a certain type of data :

As we can see, I have many rows of the same size of cells in them (4) and the number of columns change accordingly to how much data I input.

What I would like to end up with, is something like this :

Either of this version works for me, so simply put I would need to use something similar to the =TRANSPOSE() function which flips the rows 90 degrees and then somehow combine the data from all the columns into one column. Any ideas?

EDIT ^ Above problem is resolved but I got another issue.

Now I would need to make something like this:

I would need to FLATTEN() the size1_x parts and then "copy" information on the left and on the right of that row to the flattened version of the table.

The expected outcome would need to be like this :

CodePudding user response：

Use FLATTEN function:

=FLATTEN(B2:E5)

=FILTER(FLATTEN(B2:E5),FLATTEN(B2:E5)<>"")

if you don't want blanks