Split Concatenated values into distinct rows

[stj1120]

Hi,

The data in the excel file sheet1 contains the different columns with the concatenated values in each column. I now need to split each value by using the delimiter comma (,) into distinct rows like the output shown in the below screenshot.

I tried using Text to Columns tool splitting each value into multiple columns then do the transpose and then filter out if there is any empty value and then do the cross tab. But, no luck. I'm not getting the desired output. 

Output

Therefore, guide / help me with the right approach with the logic to get the exact output as expected. Output data is also present in the excel file. 

Also, let me know if there is any other approach to achieve this result without using Text to columns tool. 

Thanks & best regards,

stj

[Andrew-Merrill-CoG · Accepted solution]

Here is a workflow that doesn't use Text to Columns Tool to parse data. You do have problematic fields since the [amt] and [Est] columns have extra "," in them for decimal separation. I removed them in the workflow provided before the split and added them back after the fact. On of the most efficient ways to perform this operation is to use Generate Rows and Multi-Field Formula Tools:

Hope this helps and Happy Solving!

[flying008 · Accepted solution]

Hi, @stj1120 

FYI.

[stj1120]

Hi @Andrew-Merrill-CoG 

Thank you for your help, it is working. 

Best regards,

Stj.

[stj1120]

Hi @flying008 

Thank you for the help. Could you provide the workflow of your approach?

Best regards,

Stj

[stj1120]

Hi @Andrew-Merrill-CoG 

I need your help again. In case, the dataset has some concatenated values in few columns like Typ, Code and Amt and other columns don't have. In this case, how do I split the concatenated values and show them in the distinct rows? Input dataset is in the first sheet of the attached excel file. Second sheet is the output data. 

Kindly check the data and provide the right approach with the data as shown in the output sheet. 

Thanks & best regards,

stj.