topic Re: How do I transpose multiple delimited columns in Discussions

How do I transpose multiple delimited columns

anna_717 — Fri, 09 Jun 2023 19:50:02 GMT

Hi,

I'm trying to split and transpose multiple pipe delimited columns and would appreciate some help in finding the most efficient way of doing this.

The table contains measurement values at X,Y location for a measurement type.

The number of measurements and X,Y locations can vary for each batch and date.

Example data and required output shown below.

Start table

Output

Thanks

txnelson — Thu, 17 Jun 2021 16:22:09 GMT

Your request is an easily solvable task

To turn the concatenated data into separate columns,

Select the 3 Columns

Go to

Cols=>Utilities=>Text to Columns

Enter the "|" as the delimiter column

Click on OK

To transpose the columns go to (this is actually a stacking of the columns)

Tables=>Stack

Set up the dialog box like below

Click OK and you will get

anna_717 — Fri, 18 Jun 2021 09:29:12 GMT

Hi txnelson, Thanks for taking the time to answer this.

Unfortunatley the output you get isn't what I'm looking for, but your use of the multiple series stack did start me off in the right direction.

I realised that I need to change the order of the columns in the multiple series stack and then do a split on the data3 column.

gives

then split on data 3

gives required output: