Merge and pivot question

[zenSMP]

Hi All,

attached shows the input and output  data format that I have and want respectively. I haven't been using Alteryx for awhile so just wanted to quickly check whats the best way to solve this. Notice the first row is merge so A1 is show as 'Cat A' and  follows by F2, F3 then Cat B  etc. in this case I need to replace all the F# data with the Cat # that was leading it.

I have attached the file for your reference. if you can show the entire step or provide insights to how this can be done that would be awesome.

Thank you.

[binuacs · Accepted solution]

@zenSMP One way of doing this

[Emmanuel_G · Accepted solution]

@zenSMP 

Find attached the way of doing this.

Please do not hesitate to mark this answer as solution if it helped.