Text to rows with multiple instances

[aka_ash]

Hi,

Looking for some guidance on how I can split 2 columns and expand it to a 1 to 1 output.

Below is an input of sample data and a desired output

My data will also have data that may have spilled to the next row also and need it to be populated data from the cell above it.

I have highlighted these in orange the desired result for those.

Any assistance would be appreciated,

thanks in advance,

Ash

Input

Record ID	Items	Data 2
1A	Apple, Orange, Carrot	Left_A, Left_B, Right_C, Forward_A
		Forward_B, Back_D
1B	Orange	Left_B,Forward_C
1C	Berry, Carrot, Watermelon	Left_B, Right_C, Forward_B

Output

Record ID	Items	Data 2
1A	Apple	Left_A
1A	Apple	Left_B
1A	Apple	Right_C
1A	Apple	Forward_A
1A	Apple	Forward_B
1A	Apple	Back_D
1A	Orange	Left_A
1A	Orange	Left_B
1A	Orange	Right_C
1A	Orange	Forward_A
1A	Orange	Forward_B
1A	Orange	Back_D
1A	Carrot	Left_A
1A	Carrot	Left_B
1A	Carrot	Right_C
1A	Carrot	Forward_A
1A	Carrot	Forward_B
1A	Carrot	Back_D
1B	Orange	Left_B
1B	Orange	Forward_C
1C	Berry	Left_B
1C	Berry	Right_C
1C	Berry	Forward_B
1C	Carrot	Left_B
1C	Carrot	Right_C
1C	Carrot	Forward_B
1C	Watermelon	Left_B
1C	Watermelon	Right_C
1C	Watermelon	Forward_B

[Jean-Balteryx · Accepted solution]

Hi @aka_ash ,

Here is how you can do it !

Basically, it adds the RecordID for missing values based on previous value. Then it concatenates all rows for each record ID and it splits both Items and last column to create combinations !

Let me know if you have any question !

[atcodedog05 · Accepted solution]

Hi @aka_ash 

Here is my take on it.

Workflow:

Hope this helps : )

[messi007 · Accepted solution]

@aka_ash,

Another way to do it 🙂

Attached the workflow.

Hope this helps !

Regards.

[aka_ash]

Hi,

Thanks so much for your responses @Jean-Balteryx, @atcodedog05 and @messi007.

@Jean-Balteryx your solution was great however looks like i need an additional Multi-row formula for the "Items".

The other 2 worked well.

Thanks again all! 

[atcodedog05]

Happy to help : ) @aka_ash 

Cheers and have a nice day!

[Jean-Balteryx]

I see where I got it wrong. Using a filter at the end works but my solution is way under the two others !

[binay2448]

Find my solution for same

[aka_ash]

Hi,

Thanks for the responses, 
i realised that there was a scenario that i didn't capture in my sample data.

Regarding the data that has spilled over to the next, the data may be split inbetween the expected string.

This might also be over multiple rows, like the updated input below with the same expected output.

Is there a way to merge the extra cells to the cells above then perform the split to rows?

Record ID	Items	Data 2
1A	Apple, Orange, Carrot	Left_A, Left_B, Right_C, Forw
		ard_A, Forward
		_B, Back_D
1B	Orange	Left_B,Forward_C
1C	Berry, Carrot, Watermelon	Left_B, Right_C, Forward_B

[atcodedog05 · Accepted solution]

Hi @aka_ash 

I am using modified version of @Jean-Balteryx workflow for this. This works only for text spill overs

Workflow:

Hope this helps : )