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
[Solved] How to transpose cells
[Solved] How to transpose cells
- 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]
Reason: Tagged [Solved]
OpenOffice 4.1.7
Windows 10
Windows 10
- MrProgrammer
- Moderator
- Posts: 4895
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: How to transpose cells
Of course you were not successful. A transpose only coverts 6x3 into 3x6, never to 18x1.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.
Read the instructions carefully: [Tutorial] Rearrange rectangular data valueswithnail 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.
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).
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).
Re: [Solved] How to transpose cells
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().
(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
---
Lupp from München
Re: [Solved] How to transpose cells
Thanks for the alternative Lupp
I kept forgetting to update my profile, I'm back using OO now
I kept forgetting to update my profile, I'm back using OO now
OpenOffice 4.1.7
Windows 10
Windows 10