Macro to Convert Flat Data to Database - Amendment to existing code required

[prkhan56]

Hello All,
I am using Excel 2013

I have a macro which works perfect but now my requirement has changed

Please see the attached sample

Data - sheet - sample shown are few rows actual data is thousand+ rows
Old - sheet - is the output of the Module Main
Required - sheet - is the desired result now

Cells have been colored for explanation purposes only.
Strike out Cell are the data which is not required. Strike out only for explanation purposes

Any modification to the existing code or a New code as per my requirement would be greatly appreciated.

TIA

Rashid