Split/separate multiple fields into two other columns

Hi,

What would be the trick to split

See below for example highlighted columns - everything that is a time interval only - into the reading and attached a column that states the letter A, keeping the same time interval for the letter column, like the below-desired outcome:

Attached is a raw data example.

Many thanks !!

data ex.xlsx

Workflow

Datasets

Transformation

Preparation

Accepted answers

mceleavey

Hi @Tmanuela ,

I don't understand the logic here. Your columns don't appear to have any logical order, it goes forward and backwards in time, and some are split and other aren't.

I can say that you probably only need the Text to Columns tool to split the fields on the - symbol, but without seeing the logic of what you're trying to do it's difficult to know.

I've built the workflow which first transposes the data into a single column to make this process easier:

You then use the Text to Columns tool to split on -

I then used two Crosstab tools, one to handle value 1 and one to handle value 2.

I then joined them together and renamed (there is a bit of an issue with the Crosstab tool in that it replaces special characters with underscores).

You then end up with the following:

You can then re-sort the column orders however you like using the Select tool.

You can find out more about the Text to Columns tool HERE.

I hope this helps,

Text to Columns.yxmd

All comments

mceleavey

Hi @Tmanuela ,

I don't understand the logic here. Your columns don't appear to have any logical order, it goes forward and backwards in time, and some are split and other aren't.

I can say that you probably only need the Text to Columns tool to split the fields on the - symbol, but without seeing the logic of what you're trying to do it's difficult to know.

I've built the workflow which first transposes the data into a single column to make this process easier:

You then use the Text to Columns tool to split on -

I then used two Crosstab tools, one to handle value 1 and one to handle value 2.

I then joined them together and renamed (there is a bit of an issue with the Crosstab tool in that it replaces special characters with underscores).

You then end up with the following:

You can then re-sort the column orders however you like using the Select tool.

You can find out more about the Text to Columns tool HERE.

I hope this helps,

Text to Columns.yxmd

Tmanuela

Thanks @mceleavey Chris, my bad was I did not pay attention to the new header names but they should have been on same order as initially and you have guessed it perfect. The trick I needed was those separate crosstabs.

You rock as always

Best regards,

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703