[Solved] How to transpose cells

Discuss the spreadsheet application
Post Reply
withnail
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

[Solved] How to transpose cells

Post by withnail »

Hi all,
I'm trying to transpose some cells with values in but without success.

I have a column made up of cells containing 3 letters (H,A and D) in random order.
Each letter represents a 3 digit code -
H= 0 1 1
A = 1 1 0
D = 1 0 1

As you can see in attached example sheet I can display the 3-digit codes horizontally (B1:D6).
But I need to move them to a single column and keep everything in the correct order.
The 'paste transpose' function doesn't do what I need.
The final result would mean the column displaying the 3-digit codes would be 3 times longer than the letter column as there are 3 numbers to each letter.
Hope this makes sense.
Thanks for any tips :-)
Attachments
example.ods
(8.51 KiB) Downloaded 136 times
Last edited by robleyd on Thu Jun 25, 2020 11:26 am, edited 2 times in total.
Reason: Tagged [Solved]
OpenOffice 4.1.7
Windows 10
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: How to transpose cells

Post by MrProgrammer »

withnail wrote:I'm trying to transpose some cells with values in but without success. … As you can see in attached example sheet I can display the 3-digit codes horizontally (B1:D6). But I need to move them to a single column and keep everything in the correct order.
Of course you were not successful. A transpose only coverts 6x3 into 3x6, never to 18x1.
withnail wrote:The final result would mean the column displaying the 3-digit codes would be 3 times longer than the letter column as there are 3 numbers to each letter.
Read the instructions carefully: [Tutorial] Rearrange rectangular data values
I've tested this with the data from your attachment. If you have trouble, read the instructions again.

For the codes (columns B, C, D of your attachment)
Source parms: Rows=6 Columns=3 Up/Down=False Reverse=False
Target parms: Rows=18 Columns=1 Up/Down=False Reverse=False

For the letters (column A of your attachment)
After using Paste Special from "Instructions, Part 1" into the Source in the tutorial's attachment, copy column A of that sheet to columns B and C of the that sheet
Source parms: Rows=6 Columns=3 Up/Down=False Reverse=False
Target parms: Rows=18 Columns=1 Up/Down=False Reverse=False

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
withnail
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: How to transpose cells

Post by withnail »

That works perfectly, thanks!
OpenOffice 4.1.7
Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] How to transpose cells

Post by Lupp »

Just for "completeness" a solution working in LibreOffice V6.2 or higher:
(Or probably more for fun?)
It stresses the "new" functions TEXTJOIN() (sufficiently working since LibO v4.4) and REGEX() (since v6.2).
REGEX() is "misused" here as a surrogate for the still missing counterpart (TEXTSPLIT() ?) to TEXTJOIN().
Attachments
aoo102388specialReorganizeArray_1.ods
(10.93 KiB) Downloaded 128 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
withnail
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: [Solved] How to transpose cells

Post by withnail »

Thanks for the alternative Lupp :-)
I kept forgetting to update my profile, I'm back using OO now ;-)
OpenOffice 4.1.7
Windows 10
Post Reply