Need macro or any other way to transpose data from columns to one row

[jmc777]

Greetings! I’m stuck for ages, doing a lot of similar copy paste moves from columns to row.
Maybe there is a way to transpose this data as below:

a b c d e
a b c d e
a b c d e
a b c d e
a b c d e

to
a b c d e a b c d e a b c d e a b c d e a b c d e

macro.ods (8.9 KB)

[robleyd]

Same question at Apache OpenOffice Community Forum - move data from columns, to rows - (View topic)

If you cross post, please let us know that you have done so, otherwise it leads to several discussions and a waste of time because several identical answers may be posted by different users.

[joshua4]

Try this. What I did here is three steps:

1. Joined all of the data points using a delimiter into one text string. There may be limits to size.
2. Used REGEX to break these apart by capture group.
3. Used LEFT to get rid of the left-over delimiters.

The REGEX capture groups don’t work quite like I expected. A good REGEX person could likely eliminate Step 3 with ease.

For all cells in
$A$1:$E$33
In H8 have
=TEXTJOIN(";",0,$A$1:$E$33)&";"
Somewhere with room (here in H11) have
=TRANSPOSE(REGEX($H$8,"(.*?);",,ROW($A$1:$A$220)))
Directly below that have similar to
=LEFT(H11:HS11,LEN(H11:HS11)-1)

FuseRows.ods (16.6 KB)

Edit: add semicolon to TEXTJOIN for last trim.

[jmc777]

it works!
Questions:

1. how can i ammend formula in step 1 and step 2?
2. it truncates last item in step 2 and 3, how avoid it?

[joshua4]

@joshua4

Edit: add semicolon to TEXTJOIN for last trim.

The truncation correction is already made with the edit…just do the same in the example, add the &";" to the TEXTJOIN.

To edit an array formula, click on one of the cells in the array of cells and use Ctrl+/ to have the whole array of cells highlight itself, then edit as normal. Of course, again, press Ctrl+Shift+Enter when done editing to keep it an array formula.

I’ve replaced the uploaded file with the correction.

[Villeroy]

Apache OpenOffice Community Forum - [Tutorial] Rearrange rectangular data values - (View topic) works very well without macro code.

[Villeroy]

Reshape_t77629.ods (38.6 KB) (created within 2 minutes)

[joshua4]

Almost disingenuous to say “no macro code.” I’ve never seen programming done in the Named Values quite like that before. Very flexible.

Here’s a bit of a walk-through for interested parties.

1. To see what’s going on, use Sheet>Named Ranges>Manage or Name Box>Manage Names… to see the Named Ranges dialog. This is where all the magic happens.
2. At the core, there are two kinds of Managed Names/Expressions being defined here.
3. One kind is simply reading the values from the Parms sheet and converting them into names (variables). This is very normal stuff.
4. The second kind of named expressions are basically little lambda expressions that at their core use simple modular arithmetic to peel the source cells off the shape of the source data. At the center of these is Cell0000.
5. Cell0000 means Cell 0 0 00. The Cell is the name, the ‘high bit’ (left 0 here) is whether or not reverse order has been requested, the next ‘bit’ is up/down vs across, and the ‘lower bits’ are there to allow up to 4 reshaping actions in the same spreadsheet (I think).
6. Seeing how the rest works is a matter of tracing the named values and expressions through the Named Ranges dialog. The modular arithmetic, again, is straightforward, but certainly isn’t something to look at without eating breakfast, since it’s moving from one rasterising (modular math) to another rasterising (modular math), working from TOffset’s definition on through Cellxxxx’s OFFSET function.
7. Notice also things like the expression R0, which means 0-based row number. You can create a new sheet and just put =R0 in any cell and it will show its 0-based row number. R0 is used along with the parameters from the Parms sheet and with calculations of the total number of cells involved to get to TOffset’s definition. So R0 and C0 provide ‘location awareness’ then build up into TCountx that then builds up to TOffsetxx then to SOffsetxxx. With each level functionality builds from the location awareness to adding raster direction to then adding reversing.

[LeroyG]

EDITED: To avoid issues with cells that contain spaces, and for clarity (maybe ).

1- Select the data range and copy
2- Edit an empty cell (F2)
3.1.a- Paste or paste special as unformatted text
3.1.b- Find \n and replace with \t, then do Text to Columns separated by tab
or
3.2.a- Paste
3.2.b- Do Text to Columns, copy, then paste transposed

Maybe there are a few more simple options.

Tested with LibreOffice 7.2.7.2 on Windows 10, and with 7.2.3.2 on Linux 5.3.