Collapsing a data set

Hi

I'm trying to collapse a dataset. You should be able to see what I mean by the example I've attached.

I have a very large data set of firms. Each firm has a number of subsidiaries in certain locations. For example, in my data Wal-Mart has two subsidiaries in Chile; whilst Shell has three subsidiaries: 2 in the Philippines and 1 in Pakistan.

For each subsidiary I have data on total assets (for some years between 2002 and 2010 the data is missing but that's ok).

What I need is a formula to create a new dataset, like the one below the master. This data set is essentially each firm's exposure to a country in terms of total assets. The example shows an illustration of this.

It essentially has Wal-Mart appear once for Chile and Sums up the Total assets across each subsidiary. Shell appears twice: it sums up total assets for the 2 subsidiaries in the Philippines and includes the total assets for the subsidiary in Pakistan.

Is there a quick formula to do this? In reality each firm has lots of subsidiaries from many different locations.

Thanks very much for your help.

Sorry, not sure why this hasn't worked. This is a re-try.