Allocating Customer Numbers to Different Companies (Sums)

Apologies for the title as it is quite hard to describe.


I am looking for a solution to my problem (either simply Excel or Macro/Database based) to do the following:

- The list of customers must be allocated to different companies. They must be kept as whole as possible and preferably in order. Please see the attached Excel file for an example.

On the attachment Customer 1 has 60 (fig.1) and this has to be allocated to the different companies. Company A requires 50 (fig.2). This 50 can be allocated from Customer 1 (fig.3). Customer 1 has 10 remaining so this passes on to Company B which requires 100. The 100 can be made up of Customer 1 (10), Customer 2 (8), Customer 3 (5) and partially of Customer 4 (77 with 33 remaining to Company C).

Thanks for reading this thread. Can anyone provide any ideas on how to do this more efficiently as at the moment it is very time consuming as the lists of 'Customers' and 'Companies' can grow to be quite large.

Thanks again