VBA script for extract, merge and deleting rows

Hello Everyone,

I need some help with my spreadsheet and would like to get a VBA script that will extract data from a column, merge based on a set of criteria, and then delete unnecessary rows. Hopefully someone will be savvy enough (more than me) to make it happen.

Attached is the spreadsheet with an exampe of the data. I have some comments and notes listed, plus an example of the desired outcome in the spreadsheet.