Merge rows and split columns based on 2nd row

[dpakapd]

Hello,

I have taken some data and cleaned it such that I have it in the format shown below.

RecordID	Field_1
16	A VALUE
17	0 A B A C D E F
69	B VALUE
70	0 A B A C D E F

I would like to split it such that the data comes up as shown below:

A VALUE	0	A	B	A	C	D	E	F
B VALUE	0	A	B	A	C	D	E	F

Is this possible? I have tried splitting columns but then my A Value gets split as well and I am not sure how to merge the rows.

Can you please advise on the best way to implement this?

Thanks,

Deepak

[Qiu · Accepted solution]

@dpakapd 
Hope this would help.

[dpakapd · Accepted solution]

Thank you! This helped a lot!

Best regards,

Deepak

[Qiu]

@dpakapd 
Glad to help.
And would appreciate you mark my solution as accept, 
For now, you marked your own as accept. 😂

[dpakapd]

Haha.. My bad. I have accepted yours as the solution.