Group rows and sum the last column

My original data looks like this:

I want my data to be transformed into:

Is there any way that we can do this without using pivot/subtotals. The reason why I am against pivot/subtotals is because i want to merge this data with some other data and do some calculations. VBA holds good too.

Any ideas please?