Allocation with VBA until limit value is reached

[JM_Man]

Hello everyone,

I'm scratching my head with a problem that seems to have a simple resolution but which I can't find; so I'm in need for your help.

In a warehouse, I have several Buffer Areas to store boxes that will be shipped to several Destinations (see green table).
Each Buffer Area has its own maximum capacity (see blue table), but can only store boxes for two Destinations at the most.

What I need is a VBA code to fill the last column ("Buffer Area"), in the green table, with the designated Buffer Area to store the boxes before they are shipped to the respective destinations - the order in which the Buffer Areas are given to the Destinations should be from the first row of the table to the last one.
Ideally the end result would be what is illustrated in the orange table.

I would really appreciate any help.
Thanks!

305643056530566

[June7]

Not only do you need to know capacity of each area but also how much capacity is unused. Probably need another column in the blue table for Available. What do you want to happen when there is no area with enough capacity left to hold a box group?

Does this get you on track:

1. I built one worksheet with both tables: columns A:C for green and E:H for blue (headers on row 1)

2. column G: "Available" with formula =F2-SUMIF(C$2:C$17,E2,A$2:A$17) and column H: "DestCt" with formula =COUNTIF(C$2:C$17,E2)
make sure workbook is set for automatic calc, otherwise these calcs will have to be handled in VBA

3. VBA - I put in a general module for testing

Sub CalcLoc()
Dim x As Integer, y As Integer
For x = 2 To 17
For y = 2 To 14
If Cells(x, 1) <= Cells(y, 7) And Cells(y, 8) < 2 Then
Cells(x, 3) = Cells(y, 5)
Exit For
End If
Next
Next
End Sub
There is probably an Excel formula that could accomplish what the VBA does, but it is beyond my Excel knowledge. I made a stab at using VLookup with Min array formula but that just errored.