Automated Grouping / Total Query

Got a bit of an excel query. I was hoping to build the following functionality into the start of some existing vbscript, however if this isn't possible I was hoping to have the process automated in some other way.

I have the following Data (please see Input Data on attached spreadsheet).

Basically for each policy I want to get a total of column D and Group by all other fields. So all of the rows for a particular policy with a "Yes" in Payment Due are in one group and all of the rows with a "No" in Payment Due are in another group and the Amount Due column is merged into a single row per group

For the original data from above the desired results would be (please see Desired Output on attached spreadsheet).

Only Policy Number 000003 has changed, all of the other rows would remain the same. Hopefully this is a very easy issue to address but I haven't been able to solve it so far. Any help is much appreciated

Cheers