Split rows to new rows with using data from 2 cells

[jimmyhartington]

I have a data source of product data, which looks like this.

Input

"STYLE NUMBER","ACTIVE COLOR CODES","ACTIVE COLOR NAMES"
"160","2, 1","Grå, Hvid"
"16090","10, 2","Sort, Grå"

In this case each style has 2 colors. Defined by color codes and by a localised color name. Some products have many more colors.
I would like to get new rows where I have each style as a separate row for each color.

Desired output

"STYLE NUMBER","ACTIVE COLOR CODES","ACTIVE COLOR NAMES"
"160","2","Grå"
"160","1","Hvid"
"16090","10","Sort"
"16090"","2","Grå"

The here are many more columns in the data but I have only included these 3 in the example data.

How can this be done?

[Admin]

It’s not very pretty. But neither is your data. ;0)

jh.transform (4.9 KB)

[jimmyhartington]

Thanks. I know the data is not pretty. But it is what I have been given

[Anonymous]

@jimmyhartington

… Some products have many more colors.

Here is a solution that will cater that

Transform file.
SplitRowsToNewRows.transform (4.9 KB)

[jimmyhartington]

Thanks. This works for all. I forgot about the ones with more than 2 colors myself.

[Anonymous]

Here is another solution without JavaScript

Transform file.
SplitRowsToNewRows2.transform (5.2 KB)

[jimmyhartington]

Nice to know there a different ways to achieve it.

[Anonymous]

This is the final update, removed the need for regex in Replace, as that require change in regex, if let say there are extra characters or symbols in the data, and as regex is not that simple, came up with new solution which is simple without the regex.

Transform file.
SplitRowsToNewRows3.transform (5.1 KB)