Split columns into its own rows (comma separated in excel)

[1easy]

I have an excel which creates a new excel combining all the sheets, which works OK.

My problem is: there are multiple columns which have Comma separated values:

Example: Part No., Fit Model, etc in the attached excel

So how do I write an IF condition

IF any column has comma separated values, split it and add a new row with all the column(values0

228844, 6MR2000AAY4Z, BATT1

So in the above example:

Since E has 3 part numbers, you would create 3 new rows and copy the data to these 3 new rows. Since F has 3 model numbers, you would copy each to its own row (and all other columns copied as it is)

It would look something like this http://prntscr.com/ndvl4q

MergeSheets.xaml (8.9 KB)
ExlSheet.xlsx (14.5 KB)

[Lahiru.Fernando]

Hi @1easy

I guess this is what you are looking for?

To get this thing done, there were few things do be added. Rather than explaining point by point, I thought of doing that in the workflow and adding some comments there itself so you can watch and learn how it is done

Try it out yourself as well… You might have a better way of doing…

MergeSheets.xaml (15.6 KB)

Let me know whether this works for you!!

[1easy]

This is perfect, similarly I can do it for other columns, super

[1easy]

@Lahiru.Fernando Sorry to bother you again. I tried to do the same CSV in the next column that is column F “Fit Model”, somehow the logic is not working. I added the “Fit model” in the else condition so that similarly Fit Model gets added in its own row.

But its not working, can you please check the attached?

MergeSheets.xaml (20.4 KB)

[Lahiru.Fernando]

HI @1easy

you cannot do like this

The main condition of the IF condition is to check for PartNo array. So if you add the FitNo to the Else section of that IF, it is not going to work. your set of activities will only execute if PartNo has no values at all. That’s now how it should work…

You have to have a separate set of activities without combining with PartNo activities because those two acts differently. And also remember, PartNo also creates new rows. And when you are trying to create new rows for FitNo, you may already have existing rows which you can just insert values. However, imagine this case
PartNo has two values, which means it creates two rows right… But, for the same, FitNo has three values. So in your code, you already have two rows which you can directly insert the value. But for the third, you have to insert a new row. For this new row, your PartNo should be blank.

Try this concept… and do it without combining with the PartNo stuff because it will not work that way

Let know how it goes…