Transpose sequences of rows into columns, ending with one row per sequence

[never]

Hello, I’ve searched in both the answers and the documentation, but I can’t find a reasonably automated procedure for this: I need to transpose rows, which belong together, into columns but ending with a different row for each original sequence of rows.

Each sequence is always of 9 rows, like in this example (which I reduced to 3 instead of 9 for brevity, and with letters to illustrate the consecutive sequences):

-Original:
A1
A2
A3
B1
B2
B3
C1
C2
C3

I can transpose all rows with the “Special paste → Transpose” option, but they are all transposed as columns of the same row:

-What I can do with reasonable automation:
A1 A2 A3 B1 B2 B3 C1 C2 C3

-But this is what I need:
A1 A2 A3
B1 B2 B3
C1 C2 C3

Given that each sequence is always of 9 rows, I have considered a formula to automatically transpose them into columns, but I can’t find one in the program and macros are way above my skills.

I could insert a special character to mark the separation between each sequence, in case this might allow for an automated separation (& in this example):

A1
A2
A3
&
B1
B2
B3

Thank you in advance for your help

[Hrbrgr]

Transpose Tables in Calc
This is what the “Transpose” function can do.

Can that help?

[never]

Yes, with the Transpose function I can get:

A1 A2 A3 B1 B2 B3 C1 C2 C3

but I need to separate them like this:

A1 A2 A3
B1 B2 B3
C1 C2 C3

I was considering there might be a single step to achieve this. If there isn’t, I could perfectly just split the single transposed row, but I don’t know how.

Thank you

[Hrbrgr]

Only the marked cells will be transposed, try it. Possibly make a copy of your file beforehand.

---

Alternatively, provide your file and upload it here for someone to view.

[never]

I could only manually transpose a sequence at a time, but I will need to do it daily/weekly on new material because it’s part of my new studying technique. This is the reason why I was looking for a way to automate this input.

[Hrbrgr]

Well, that’s probably where a macro would come in useful.
Unfortunately, I’m out of that, it’s not my world.

[Lupp]

I wouldn’t try an answer as long as there isn’t clearly explained what a sequence is in the context.

• Are all the sequences of same length?
• Is the start and/or the end of a sequence recognized based on the content in a specific way (without resorting to additionally inserted rows) ?

There were, of course, similar questions, and also there were suggested some solutions.
However, the preferable solution will depend o the situation.

In the case of sequences of same length, you find a spreadsheet document with an engine for the task here.
Solutions for this case based on user code were also published.

Different cases need thorough explanation.

[never]

• Are all the sequences of same length?

Yes, they are always 9 rows and always 1 column. In my previous example I used 3 rows instead of 9 for brevity (as I explained), but that was the operation I needed.

• Is the start and/or the end of a sequence recognized based on the content in a specific way (without resorting to additionally inserted rows) ?

No, it’s always a fixed 9 rows and 1 column, resulting in a sequence of several consecutive groups of 9 rows inside the same column. Since it was a fixed length, I thought there might be an included formula, like there is one for just transposing.

Thank you for the document link , it works for my use case and also for several related operations.

[Lupp]

@never

• Are all the sequences of same length?

Yes, they are always 9 rows and always 1 column. In my previous example I used 3 rows instead of 9 for brevity (as I explained), but that was the operation I needed.

Well, in this case also a “macro”, here a user defined function, might be useful if the task is recurring. @Hrbgr mentioned this already. The attached example contains my toolbox of related macros, of which, however, only one is used by the suggested formula.
If you want to try it, you need to set your macro security to medium, and to permit macro execution for the example.
Example file:
disask73829RearrangeArray.ods (20.4 KB)

[JohnSUN]

In general view, your TRANSPOSE() should look like this:

{=TRANSPOSE(OFFSET(<$first_cell_of_source_data>;<count_of_values_in_row>*(ROW()-ROW(<$first_cell_of_target_range>));0;<count_of_values_in_row>;1))}

For example, if your source data column starts from cell B4, and the range where you are going to transpose these values will be E6:M12 (only the first cell, E6, will be needed for the formula), then the formula will be:
{=TRANSPOSE(OFFSET($B$4;9*(ROW()-ROW($E$6));0;9;1))}

If the data starts from the upper left corner of the sheet, from cell A1, and the result will also be placed starting from the first line, then the formula will be simpler:
{=TRANSPOSE(OFFSET($A$1;9*(ROW()-1);0;9;1))}

Since the TRANSPOSE() function is an array function, the formula input should be ended with the Shift+Ctrl+Enter key combination

I don’t like to advise beginners on array functions - their use requires certain skills (select the entire array with Ctrl+/ before editing, don’t forget to press Shift+Ctrl+Enter and a few more obligatory moves that will come with experience).

For example, to use the above formula, you will have to enter it in one cell and press Shift+Ctrl+Enter - you will get 9 cells per row with values A1-A9. Now copy the entire resulting range and paste it into the cells below (or drag it down with the mouse while holding down the Ctrl key). If you immediately select a rectangular range, enter the formula and press Shift+Ctrl+Enter the result will be, but it will be completely different, not what you expected to get.

Therefore, I suggest you forget about the TRANSPOSE() function for a while and use only OFFSET(). Just add the current cell column to the formula:
=OFFSET($B$4;9*(ROW()-ROW($E$6))+(COLUMN()-COLUMN($E$6));0)

[Lupp]

@JohnSUN: Concerning generalizing solutions by user code, you may be interested in a somehow playful (but efficient) function written in Basic, and named PasDeDeux in a mood. Unfortunately I’m not sure if I already usedf it in a previous thread somewhere. It is contained in the attached example:
rearrangeByPasDeDeux.ods (18.8 KB)
(My related local toolbox has more means for rearrangement.)

[never]

Thank you so much for your thoroughly detailed, newbie friendly explanation and for your precise formulas

In Calc I was only able to use basic functions, but following your excellent instructions I could get both your array and non-array functions to work. In the array function, for some reason I could even expand the cells much more easily than I have any skill or knowledge for.

Once the sheet template is ready for a magnitude of hundreds of rows of input, I can just convert my sequence with one paste, which is magic compared to manually editing a transposed row.

Thank you again