• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sales allocation against receipts

jainpiyush2609

New Member

Attachments

  • sales allocation against receipts.xlsx
    8.5 KB · Views: 11

jainpiyush2609

Without Your answers I solved this 'my way'
Usage: press [ Do It ]-button to get output.
It seems to work with Your given data.
 

Attachments

  • sales allocation against receipts.xlsb
    19.5 KB · Views: 8
Hello vletm ! Hoping the initial post was not really for Google Sheets or you wasted your time …​
 
Hii vletm. Thank you so much for your help. you are absolute genius. I tried changing amount from data 1 and data 2 to check whether code is working . there is some issue with Receipt 2 of Data 2. I have attached file with the changed number and desired output for your perusal. Hope you can help me .
 

Attachments

  • sales allocation against receipts.xlsb
    23.6 KB · Views: 15

jainpiyush2609

Did You really miss all my questions?
I can & will test these only with given data.
You found something ... okay ... There could be something else too.
Is there any reason - below - why yellow cell 5 should be missing?
Screenshot 2024-06-17 at 19.08.17.png
I attach my the latest versio; there are to samples.
 

Attachments

  • sales allocation against receipts.xlsb
    24.5 KB · Views: 10
data which is lenghty
According to your crystal clear post #8 attachment a classic efficient fast short VBA arrays demonstration, result in columns I-L :​
Code:
Sub Demo1()
  Const C = "I:L"
    Dim V(1), W(), R, N%, L&
        If [OR(A4=0,E4=0)] Then Beep: Exit Sub
        V(0) = Range("A4", [A3].End(xlDown)(1, 3))
        V(1) = Range("E4", [E3].End(xlDown)(1, 3))
        ReDim W(1 To (UBound(V(0)) + UBound(V(1))) * 2, 1 To 4)
        R = Array(1, 1)
   With Columns(C).Rows(2).CurrentRegion.Rows
        If .Count > 2 Then .Item("3:" & .Count).Clear
   End With
   Do
        N = -(V(1)(R(1), 3) < V(0)(R(0), 3))
        L = L + 1
        W(L, 1) = V(N)(R(N), 3):  W(L, 2) = V(0)(R(0), 1):  W(L, 3) = V(1)(R(1), 1):  W(L, 4) = W(L, 1)
    For N = 0 To 1
        V(N)(R(N), 3) = V(N)(R(N), 3) - W(L, 1)
        If CCur(V(N)(R(N), 3)) = 0 Then If R(N) < UBound(V(N)) Then R(N) = R(N) + 1 Else V(N)(R(N), 3) = 0
    Next
   Loop While V(0)(R(0), 3) * V(1)(R(1), 3)
        N = -(V(1)(R(1), 3) > 0)
     If V(N)(R(N), 3) Then
     Do
         L = L + 1
         W(L, 1 + N * 2) = V(N)(R(N), 3 - N * 2):  W(L, 2 + N * 2) = V(N)(R(N), 1 + N * 2)
         R(N) = R(N) + 1
     Loop Until R(N) > UBound(V(N))
     End If
   With Rows(4).Resize(L).Columns(C)
       .Borders.Weight = 2:  .BorderAround , 3
       .Item("B:C").HorizontalAlignment = xlCenter
        Union(.Item(1), .Item(4)).NumberFormat = " # ###_W"
       .Value = W
   End With
   With Rows(4 + L).Columns(C)
        With Union(.Item(1), .Item(4)):  .BorderAround , 3:  .FormulaR1C1 = "=SUM(R[-" & L & "]C:R[-1]C)":  End With
       .Item(1).Borders(10).Weight = 2:  .Item(4).Borders(7).Weight = 2
   End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
jainpiyush2609 : if your initial post is really about some formulas way then indicate what is your Excel version​
in order the formulas helpers may see if such formulas way is doable, and before this thread moved to the VBA Macros forum section …​
 
Back
Top