Splitting single row into 2 based on a column

[Jasdev]

Hi.

I have a scenario for which I have been thinking from a lot time but couldn't figure out how it can be done. 

My input looks like

col1	col2	col3	col4	col5
a			1	2
b	3	4
c
d	5	6	7	8

My output should look like

col1	op2	op3
a	1	2
b	3	4
c
d	5	6
d	7	8

I can figure out a way where either of col2, col3 or col4 col5 are populated, i can use if else to get output. I am not understanding the scenario of splitting col1 where all col2,3,4,5 are populated so I have to split them in 2 different rows. Here, col2 and col3 and one set like cost and quantity for one scenario and col4 and col5 are cost and quantity of different scenario so theyll have values together or will be 0 together. Is there a way to generate the rows completely and split these 2 different scenarios? 

[TomWelgemoed]

Out of curiousity, why does Ram get 2 rows?

[Jasdev]

Well Ram can get 1 also or to be frank I dont really care about Ram, We can filter out those entries to a seperate browse for this scenario where type 1 and type 2 quantity and cost are empty. 

[TomWelgemoed · Accepted solution]

Hi @Jasdev ,

You owe me half an hour of my life! 😉 Was more tricky than I thought.

Think the attached does it. We even accommodate good ol' Ram. 🙂

Think the main key was to split the column numbers from the pivoted columns and then to concatenate onto the same row the different values for the different columns. That allows you to identify the cases where there needs to be 2 rows or 1 row.

Will make more sense when you work through the workflow, so I attach that and an image. 

Best,
Tom

[Jasdev]

@TomWelgemoed  sir you are amazing! this really worked in my scenario. Apologies for the delayed response as I was testing this workflow based on my scenario.

I know it was a complicated one and thank you for taking the important 30 minutes of your life to help me with this. I owe you a treat for sure 😄

Thanks 🙂

[TomWelgemoed]

Hi @Jasdev ,

Glad it worked! And no worries about the 30 minutes 😉 I just underestimated the problem.

Would you mind to accept the answer as a solution? 

All the best,

Tom

[Jasdev]

I accepted it, thanks once again.

[TomWelgemoed]

Thank you!