Formatting of data in excel

Hi

I have a data in an unorganised format. Can anyone help me with a macro to organise the data in the required format in the next worksheet. The excel sheet is attached for your reference. Thanking you in advance.

Hi

Thanks for your reply. It would be of great help if I could get the end result in form of macro so that whatever be the length of data. With a click of a button I could get the end result.