Excel Data Extract

Monthly,I download, from a third-party vendor, a detailed accounts receivable report for our company that consists of roughly 1000 patient names, insurance company, amount they own, charge number, etc. When downloaded, this data is automatically exported to Excel with no other options. I've asked for other formats like XML, CSV by patient, but no luck. I have to reformat this report monthly to a summary report, which take about 12 hours for all clients. I've been playing with vlookup, hlookup, and now macros but I'm running into road blocks. I've attached two images. Image Excel Report 1 depicts the report in it's native view, full detail. The second image is Excel Report 2, is how I need to format the report. I started writing a macro with "find, offsets, copy and paste" but I'm very new at this and was looking for some guidance. I also attached an Excel spreadsheet with the same data..

Any help is appreciated as I don't want to go down the wrong road.