Transpose rows of data to column, with 1st cell in row repeated

Hi,

Difficult one to explain so I have mocked up an example attached. I found a formula online that works but only for a maximum of 3 cells in the row. Each row can contain more or less than 3 cells of data, please could you help modify this formula or suggest a new formula to achieve how I would like the final columns of data to look in the example.

Appreciate your help, been scratching my head for ages!

Thanks,
Adam.