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 |
Solved! Go to Solution.
Great solution, thanks @atcodedog05 and @Jean-Balteryx
You are welcome @aka_ash ! 🙂