Rearrange Data - Sheet 1 into Sheet 2 Macro?

Pulling my hair out, hoping someone can help. I'm looking for a macro to turn data in sheet 1 into sheet 2. Thank you.

Thank you very much and apologies for the cross posting. Carim from the Ozgrid kindly took some time to create the attached. Also thank you to Alan for your input. Greatly appreciated!

---

Cross Posts

Rearranging Data, Sheet 1 into Sheet 2
https://www.mrexcel.com/board/thread...eet-2.1165012/
https://www.excelforum.com/excel-pro...-2-macro.html/
http://www.excelforum.com/excel-prog...t-2-macro.html

are you sure your expected result is correct?
because I got
Attachment 3547

Could you explain your logic from source data to the result?