[Solved] Transpose cells

[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

[MrProgrammer]

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.

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.

[withnail]

That works perfectly, thanks!

[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().

[withnail]

Thanks for the alternative Lupp
I kept forgetting to update my profile, I'm back using OO now