Split/separate multiple fields into two other columns

[Tmanuela]

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 !! :)

M.

[mceleavey · Accepted solution]

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,

M.

[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,

M.

[mceleavey]

😁