Flatten an array of data into 1 row

Hello Excel Wizards,
I like to know whether VBA could help in this case...
So I have a table that I wish to convert to the table on the right. Basically, it's to flatten the rows into 1 single row. And row starts with a common denominator "orbs".
And when there is a blank in the column, I want that to be reflected as a blank in the new table too (eg. D6 = P3)
The actual data has 50 over columns and thousands of rows, so should I use a formula for this or am I right to sense that VBA is better for this?

So if I imagine I have a rolling pin, I would flatten each array of "orbs" data, and put the rows together (i.e. remove the blank rows).
Sometimes the "orbs" appear earlier or later depending on how thick the data is...

PS: I tried using some concatenation and offset formula and it massively slowed down the sheet till it's not usable.

Capture.PNG

Try this code:

Please Login or Register  to view this content.

Thanks for the great code. Works perfectly.
Just a slight extension... what if I have this static data at the left,
How could I incorporate that into the final output too?
There's some variation in the actual static data, so just to be sure, how could we copy over the columns on the left?
Do we just extend the range of this VB?



Capture2.PNG