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

[aka_ash]

Great solution, thanks @atcodedog05 and @Jean-Balteryx 

[Jean-Balteryx]

You are welcome @aka_ash ! 🙂