Distribute a total value evenly to an amount of cells considering certain conditions

[jorgebeto16]

Hi to all,

I wonder if someone could help me with a VBA code (formula) or excel formula in order calculate a distribution:

EJEMPLO.png

As shown in the image, i want to make a distribution of a total value between A to F (B2: F2),

The formula should consider:

1) Distribute the amounts considering the demand as a priority.
2) It should distribute in a way where every Segment A to F receives 1 unit in order till the total value stocks out.
3) When a DEMAND is fullfilled it should pass to the next Segment.
4) ONLY when all demands are fullfilled it should asign evenly 1 unit to all segments until it stocks out.

Example:

Total value = 6

The distribution should start with

E =1 then
D= 1 then
C= 1 then
B= 1 then
A= 1 till now 5 units are asgined then it should start with E again

Final result:

E =2
D= 1
C= 1
B= 1
A= 1

Thank you!

[DarkF1ame]

@jorgebeto16

I have come up with the below code that I believe will serve your purposes:

Sub allocate()

Dim equal_count As Integer
Dim remainder As Integer

For rw = 1 To Range("A2", Range("A1").End(xlDown)).Rows.Count

equal_count = Application.WorksheetFunction.RoundDown(Cells(rw + 1, 1).Value / 5, 0)

remainder = Cells(rw + 1, 1) - (equal_count * 5)

Range(Cells(rw + 1, 2).Address, Cells(rw + 1, 6).Address).Value = equal_count
            
If remainder <> 0 Then

    For demand = 1 To 5
    
        If Cells(rw + 1, demand + 9) >= 6 - remainder Then
        
            Cells(rw + 1, demand + 1) = Cells(rw + 1, demand + 1) + 1
            
        End If
    
    Next demand

End If

Next rw

End Sub

This should accurately perform what you want. I have tested this in my own workbook and the results seem correct:
Priority Allocation.PNG

As a note this particular code would need to be ran after every change to your data set. Other sources would enable you to utilize automation based on cell changes, etc. Please let me know if this is not close enough to be adapted to your immediate needs.

Thanks,

DarkF1ame

P.S. - If this does indeed resolve your issue please mark this thread as "Solved".

[jorgebeto16]

@DarkF1ame

It's pretty close, and it has helped me a lot to understand how it will possibly work, but i need it to follow the conditions, i think i can explain me better :

DISTRIBUTION TABLE.png

In the example :

Total value 20:
// The formula is supposed to assign 1 unit to every segment as long as it never surpass it's total value and the order will start from Max to min

A B C D E
1 1 1 1 1 TOTAL 5 //Remaining 15

A B C D E
2 2 2 2 2 TOTAL 10 //Remaining 10 ---> At this point SEGMENT "D" Has reached its total demand (2), so the formula/ code needs to exclude this segment and
stop assigning more units for now.

A B C D E
3 3 3 2 3 TOTAL 15 //Remaining 5 ---> At this point SEGMENT "B & E" Has reached its total demand (3), so the formula/ code needs to exclude this segment
and stop assigning more units for now.

A B C D E
5 3 7 2 3 TOTAL 20 //Remaining 0 ---> At this point NEITHER SEGMENT "A or C" Has reached its total demand BUT the total amount has stock out so it wont
assign any more units.

Consider also example
TOTAL VALUE 30 (ROW 3):

A B C D E
12 4 5 3 6 TOTAL 30 // Remaining 0 -- > ALL SEGMENTS reach its demand and after that the code assigns 1 unit per segment following its DEMAND ORDER
(MAX to min)

How many units each segment receive in addition to its original demand?:

A B C D E
2 1 1 1 1 As you can notice only SEGMENT "A" received 2 additional units, thats because the code will assign 1 Unit to each SEGMENT starting with the one
that has Highest DEMAND


Thanks for your help, hope i have been clear with the examples

[xladept]

Try this:

Sub Jorgebeto(): Dim r As Long, T As Single, n As Long
For r = 2 To Range("A" & Rows.Count).End(xlUp).Row
                T = Cells(r, 1).Value
Cells(r, 2).Resize(1, 5).Value = Int(T / 5): T = T Mod 5
For n = 1 To T: Cells(r, 7 - n) = Cells(r, 7 - n) + 1: Next n
Next r
End Sub