Concatenate Data with conditions

[alteryxkev]

Hi all, 

I have a dataset that I'm having some issues with, within the screenshot below the target state of the data is shown marked in green. 

Where my issue occurs is when I have multiple subscription IDs from column 6. The text to columns tool has been used to break the data out by their column volume, which is a comma, but I want to be able to do is rejoin the subscription IDs and bring the other columns back into line so I can one set of transformation to the column headers etc later on in the workflow process. 

I have attached a copy of the data below also. 

Thanks in advance for any help offered! 

[Zok]

Hello

I don't understand what you have as an input, what you want and what you get. Perhaps someone will, but maybe you can give more details.

[alteryxkev]

Hi Zok, 

The rows that are coloured green is the desired target state for all rows. 

The bottom two rows have multiple subscription IDs that I would like to concatenate into the first row, which would then bring the other columns into line for further processing. 

[alteryxkev]

I've also attached what my desired outcome is, to remove any doubts. 

[Zok]

is that working for you ?

[Christina_H · Accepted solution]

Try this:

[PangHC · Accepted solution]

since the issue is due to the text to column tool. did you mind to share the data before that?

because you can change the delimiter:

1. add a formula tool, to replace ",cf_" to ",|cf_"
2. text to columns to split by "|"

*if first few column not contains "cf_", split them first.

[alteryxkev]

Thats genius, why didn't I think of that! 

[alteryxkev]

Hi Christina, this works too, thanks for having a look at it for me!