Qty Allocation Solution

S

sanket_sk

Board Regular
Joined
Dec 27, 2016
Messages
140
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Happy new year in Advance!!!

Friends i am stuck up on automating quantity allocation sheet in excels, the logic is as follows..

We have order sheet and ETA ( Expected Time of Arrival) sheet.

Once party place order we want to share ETA against each order but there might be cases that same material will have different ETAs based on Order PO qty.

Sharing example of two material codes, 1> Material code 953681 - our 1st PO order quantity for this code is 70 which will reach in WK2001 and second lot (100Qty) will reach WK2003.

Party order details available in Order Sheet, where we want formula to some order qty and start allocating qty based on PO order Qty, now here first order qty for 953681 is 70, so formula to some order qty and allocate these qty and put respective ETA number, now for rest of the order qty formula to go to second order qty which is 100 and start allocating qty, there may be more PO order Qty or orders formula should take care of all and in case party order is greater than PO order qty formula to keep allocating till order qty is there and for rest of the qty it should say " Will share ETA shortly"

I know this is bit confusing and Thought-provoking formula but I am confident our expert team will definitely help me in developing it for sure

Order Data.xlsx
ABCDEFGHIJK
2Formula should allocate qty hereETA should come here
3Document DateSold-To PartyName 1Sales DocumentPurchase Order NumberItem (SD)MaterialDescriptionOrder QuantityAllocation QtyETA
409-09-20953721Pot1010WK2002
515-09-20953721Pot1010WK2002
618-09-20953721Pot1212WK2002
721-09-20953681Lid11WK2001
822-09-20953721Pot22WK2002
923-09-20953721Pot55WK2002
1026-09-20953721Pot100100WK2002
1128-09-20953721Pot1010WK2002
1230-09-20953721Pot1010WK2002
1302-10-20953721Pot1010WK2002
1403-10-20953721Pot33WK2002
1503-10-20953681Lid55WK2001
1605-10-20953681Lid1515WK2001
1707-10-20953721Pot1010WK2002
1807-10-20953721Pot44WK2002
1907-10-20953721Pot1010WK2002
2010-10-20953721Pot1010WK2002
2110-10-20953721Pot1212WK2002
2212-10-20953721Pot30082,218WK2002, WK2005
2312-10-20953721Pot1010WK2005
2412-10-20953681Lid2020WK2001
2514-10-20953681Lid44WK2001
2614-10-20953681Lid33WK2001
2715-10-20953681Lid1010WK2001
2821-10-20953681Lid22WK2001
2922-10-20953681Lid2010,10WK2001 , WK2003
3026-10-20953681Lid55WK2003
3126-10-20953681Lid1010WK2003
3229-10-20953681Lid22WK2003
3304-11-20953681Lid22WK2003
3407-11-20953681Lid1515WK2003
3510-11-20953681Lid1313WK2003
3611-11-20953681Lid1010WK2003
3717-11-20953681Lid55WK2003
Order Sheet


Order Data.xlsx
ABCDE
1MaterialDescriptionOrder PO QtyETAReference
2953721Pot300WK2002953721_1
3953681Lid70WK2001953681_1
4953721Pot300WK2005953721_2
5953681Lid100WK2003953681_2
ETA Sheet
Cell Formulas
RangeFormula
E2:E5E2=A2&"_"&COUNTIF($A$2:A2,A2)



Thanks & Regards,
Sanket
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
sanket_sk

There are some things that aren't clear here.

1. What is the connection between Order Quantity and Allocation Quantity? For example, in row 10 an order of 100 is allocated 100 but in row 22 an order of 300 is broken up into 2 parts of 82 and 218. How is that decided?

2. What does the ETA code mean and what is the criteria for allocating it? In other words, why do some orders get ETA code WK2001 and others get WK2002?
 
Upvote 0
Assuming ...
a) the table on 'Order Sheet' is a formal Excel table (ListObject) and is ListObject(1) on that sheet
b) the 'Allocation Qty' and 'ETA' columns already exist in the table with headings only
c) there is sufficient 'Order PO Qty' in Sheet2 to cover any orders in 'Order Sheet'

... then you could try this macro with a copy of your workbook.

Note that I have used "|" to divide shared values in a row to avoid any confusion with thousand separator in the 'Allocation Qty' column.

VBA Code: 
Sub QtyAllocation()
  Dim d As Object
  Dim a As Variant, b As Variant, Mat As Variant
  Dim i As Long, OrderQty As Variant, AvailQty As Long
  Dim AllocQty As String
  
  With Sheets("Sheet2")
    a = Application.Index(.Range("A1").CurrentRegion.Value, Evaluate("row(2:" & .Range("A1").End(xlDown).Row & ")"), Array(1, 3, 4))
  End With
  Set d = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a)
    d(a(i, 1)) = d(a(i, 1)) & "|" & a(i, 2) & "#" & a(i, 3)
  Next i
  With Sheets("Order Sheet").ListObjects(1).DataBodyRange
    a = Application.Index(.Value, Evaluate("row(1:" & .Rows.Count & ")"), Array(7, 9))
    ReDim b(1 To UBound(a), 1 To 2)
    For i = 1 To UBound(a)
      Mat = a(i, 1)
      AvailQty = Val(Mid(Split(d(Mat), "#")(0), 2))
      OrderQty = a(i, 2)
      AllocQty = IIf(AvailQty < OrderQty, AvailQty, OrderQty)
      b(i, 1) = IIf(Len(b(i, 1)) > 0, b(i, 1) & "|" & AllocQty, Val(AllocQty))
      AvailQty = AvailQty - AllocQty
      b(i, 2) = IIf(Len(b(i, 2)) > 0, b(i, 2) & "|", "") & Split(Split(d(Mat), "|")(1), "#")(1)
      If AvailQty > 0 Then
        d(Mat) = "|" & AvailQty & Mid(d(Mat), InStr(1, d(Mat), "#"))
      Else
        d(Mat) = Mid(d(Mat), InStr(2, d(Mat) & "|", "|"))
      End If
      OrderQty = OrderQty - AllocQty
      If OrderQty > 0 Then
        a(i, 2) = OrderQty
        i = i - 1
      End If
    Next i
    .Columns(10).Resize(, 2).Value = b
  End With
End Sub

My results

sanket_sk.xlsm
GHIJK
3MaterialDescriptionOrder QuantityAllocation QtyETA
4953721Pot1010WK2002
5953721Pot1010WK2002
6953721Pot1212WK2002
7953681Lid11WK2001
8953721Pot22WK2002
9953721Pot55WK2002
10953721Pot100100WK2002
11953721Pot1010WK2002
12953721Pot1010WK2002
13953721Pot1010WK2002
14953721Pot33WK2002
15953681Lid55WK2001
16953681Lid1515WK2001
17953721Pot1010WK2002
18953721Pot44WK2002
19953721Pot1010WK2002
20953721Pot1010WK2002
21953721Pot1212WK2002
22953721Pot30082|218WK2002|WK2005
23953721Pot1010WK2005
24953681Lid2020WK2001
25953681Lid44WK2001
26953681Lid33WK2001
27953681Lid1010WK2001
28953681Lid22WK2001
29953681Lid2010|10WK2001|WK2003
30953681Lid55WK2003
31953681Lid1010WK2003
32953681Lid22WK2003
33953681Lid22WK2003
34953681Lid1515WK2003
35953681Lid1313WK2003
36953681Lid1010WK2003
37953681Lid55WK2003
Order Sheet
 
Upvote 0
Peter_SSs said:
Assuming ...
a) the table on 'Order Sheet' is a formal Excel table (ListObject) and is ListObject(1) on that sheet
b) the 'Allocation Qty' and 'ETA' columns already exist in the table with headings only
c) there is sufficient 'Order PO Qty' in Sheet2 to cover any orders in 'Order Sheet'

... then you could try this macro with a copy of your workbook.

Note that I have used "|" to divide shared values in a row to avoid any confusion with thousand separator in the 'Allocation Qty' column.

VBA Code: 
Sub QtyAllocation()
  Dim d As Object
  Dim a As Variant, b As Variant, Mat As Variant
  Dim i As Long, OrderQty As Variant, AvailQty As Long
  Dim AllocQty As String
 
  With Sheets("Sheet2")
    a = Application.Index(.Range("A1").CurrentRegion.Value, Evaluate("row(2:" & .Range("A1").End(xlDown).Row & ")"), Array(1, 3, 4))
  End With
  Set d = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a)
    d(a(i, 1)) = d(a(i, 1)) & "|" & a(i, 2) & "#" & a(i, 3)
  Next i
  With Sheets("Order Sheet").ListObjects(1).DataBodyRange
    a = Application.Index(.Value, Evaluate("row(1:" & .Rows.Count & ")"), Array(7, 9))
    ReDim b(1 To UBound(a), 1 To 2)
    For i = 1 To UBound(a)
      Mat = a(i, 1)
      AvailQty = Val(Mid(Split(d(Mat), "#")(0), 2))
      OrderQty = a(i, 2)
      AllocQty = IIf(AvailQty < OrderQty, AvailQty, OrderQty)
      b(i, 1) = IIf(Len(b(i, 1)) > 0, b(i, 1) & "|" & AllocQty, Val(AllocQty))
      AvailQty = AvailQty - AllocQty
      b(i, 2) = IIf(Len(b(i, 2)) > 0, b(i, 2) & "|", "") & Split(Split(d(Mat), "|")(1), "#")(1)
      If AvailQty > 0 Then
        d(Mat) = "|" & AvailQty & Mid(d(Mat), InStr(1, d(Mat), "#"))
      Else
        d(Mat) = Mid(d(Mat), InStr(2, d(Mat) & "|", "|"))
      End If
      OrderQty = OrderQty - AllocQty
      If OrderQty > 0 Then
        a(i, 2) = OrderQty
        i = i - 1
      End If
    Next i
    .Columns(10).Resize(, 2).Value = b
  End With
End Sub

My results

sanket_sk.xlsm
GHIJK
3MaterialDescriptionOrder QuantityAllocation QtyETA
4953721Pot1010WK2002
5953721Pot1010WK2002
6953721Pot1212WK2002
7953681Lid11WK2001
8953721Pot22WK2002
9953721Pot55WK2002
10953721Pot100100WK2002
11953721Pot1010WK2002
12953721Pot1010WK2002
13953721Pot1010WK2002
14953721Pot33WK2002
15953681Lid55WK2001
16953681Lid1515WK2001
17953721Pot1010WK2002
18953721Pot44WK2002
19953721Pot1010WK2002
20953721Pot1010WK2002
21953721Pot1212WK2002
22953721Pot30082|218WK2002|WK2005
23953721Pot1010WK2005
24953681Lid2020WK2001
25953681Lid44WK2001
26953681Lid33WK2001
27953681Lid1010WK2001
28953681Lid22WK2001
29953681Lid2010|10WK2001|WK2003
30953681Lid55WK2003
31953681Lid1010WK2003
32953681Lid22WK2003
33953681Lid22WK2003
34953681Lid1515WK2003
35953681Lid1313WK2003
36953681Lid1010WK2003
37953681Lid55WK2003
Order Sheet
Click to expand...
Hi Peter,

Wishing you a very very happy, prosperous and healthy year 2021!!!

Thanks a lot for your support, code is working fine for me but I have one challenge.

I are not sure about Order PO Qty and there might be cases where this qty is less than Order Quantity, in such scenario we want to put text in "Will share ETA shortly" in Order sheet ETA column.

Could you please help build logic.

Sanket
 
Upvote 0
ExcelGzh said:
sanket_sk

There are some things that aren't clear here.

1. What is the connection between Order Quantity and Allocation Quantity? For example, in row 10 an order of 100 is allocated 100 but in row 22 an order of 300 is broken up into 2 parts of 82 and 218. How is that decided?

2. What does the ETA code mean and what is the criteria for allocating it? In other words, why do some orders get ETA code WK2001 and others get WK2002?
Click to expand...

Hi ExcelGzh,

Wish you very happy, prosperous and healthy new year !!!

Regarding ETA, ( Expected time of arrival ) - This is tentative information to our partners about spare availability or in another words info on spare when it be available.

WK2001 ( used just for example ) WK2001 means year2020 week 1 - so partner will be able to understand by when he will get material.

Regarding allocation and order qty relation -

Order Qty mentioned in Order Sheet - This is order placed by Partner
Order PO Qty mentioned in ETA Sheet - This is PO raised by me at Comaker -

I will be able to fulfill partner order once i get material from my comaker, in such scenario if partner order is 100 and i have raised PO for same material say 70 & 50 Qty, allocation should be done 70 qty from my 1st PO to vendor and 30 from second PO, also respective ETA should available in ETA column.

Hope this clarifies.

Sanket
 
Upvote 0
sanket_sk said:
I are not sure about Order PO Qty and there might be cases where this qty is less than Order Quantity, in such scenario we want to put text in "Will share ETA shortly" in Order sheet ETA column.
Click to expand...
To clarify, what would go in J4 and K4 of Order Sheet if Sheet2 was

sanket_sk.xlsm
ABCDE
1MaterialDescriptionOrder PO QtyETAReference
2953721Pot4WK2002953721_1
3953681Lid70WK2001953681_1
4953721Pot1WK2005953721_2
5953681Lid100WK2003953681_2
Sheet2


and Order Sheet was

sanket_sk.xlsm
GHIJK
3MaterialDescriptionOrder QuantityAllocation QtyETA
4953721Pot10
Order Sheet


Would it be
J4: 4|1|5
K4: WK2002|WK2005|Will share ETA shortly

or would it be
J4: Blank
K4:Will share ETA shortly

or would it be something else?
 
Upvote 0
Peter_SSs said:
To clarify, what would go in J4 and K4 of Order Sheet if Sheet2 was

sanket_sk.xlsm
ABCDE
1MaterialDescriptionOrder PO QtyETAReference
2953721Pot4WK2002953721_1
3953681Lid70WK2001953681_1
4953721Pot1WK2005953721_2
5953681Lid100WK2003953681_2
Sheet2


and Order Sheet was

sanket_sk.xlsm
GHIJK
3MaterialDescriptionOrder QuantityAllocation QtyETA
4953721Pot10
Order Sheet


Would it be
J4: 4|1|5
K4: WK2002|WK2005|Will share ETA shortly

or would it be
J4: Blank
K4:Will share ETA shortly

or would it be something else?
Click to expand...

Hi Peter,

It will be like this >>>

J4: 4|1|5
K4: WK2002|WK2005|Will share ETA shortly

Thanks & Regards,
Sanket
 
Upvote 0
Thanks.
Not sure that I have it all covered, but try code below.

If it fails, please give specific sample data, expected results and explanation.

VBA Code: 
Sub QtyAllocation_v2()
  Dim d As Object
  Dim a As Variant, b As Variant, Mat As Variant
  Dim i As Long, OrderQty As Variant, AvailQty As Long
  Dim AllocQty As String
  
  With Sheets("Sheet2")
    a = Application.Index(.Range("A1").CurrentRegion.Value, Evaluate("row(2:" & .Range("A1").End(xlDown).Row & ")"), Array(1, 3, 4))
  End With
  Set d = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a)
    d(a(i, 1)) = d(a(i, 1)) & "|" & a(i, 2) & "#" & a(i, 3)
  Next i
  With Sheets("Order Sheet").ListObjects(1).DataBodyRange
    a = Application.Index(.Value, Evaluate("row(1:" & .Rows.Count & ")"), Array(7, 9))
    ReDim b(1 To UBound(a), 1 To 2)
    For i = 1 To UBound(a)
      Mat = a(i, 1)
      If Len(d(Mat)) > 0 Then
        AvailQty = Val(Mid(Split(d(Mat), "#")(0), 2))
      Else
        AvailQty = OrderQty
        d(Mat) = "|#Will share ETA shortly"
      End If
      OrderQty = a(i, 2)
      AllocQty = IIf(AvailQty < OrderQty, AvailQty, OrderQty)
      b(i, 1) = IIf(Len(b(i, 1)) > 0, b(i, 1) & "|" & AllocQty, Val(AllocQty))
      AvailQty = AvailQty - AllocQty
      b(i, 2) = IIf(Len(b(i, 2)) > 0, b(i, 2) & "|", "") & Split(Split(d(Mat), "|")(1), "#")(1)
      If AvailQty > 0 Then
        d(Mat) = "|" & AvailQty & Mid(d(Mat), InStr(1, d(Mat), "#"))
      Else
        d(Mat) = Mid(d(Mat), InStr(2, d(Mat) & "|", "|"))
      End If
      OrderQty = OrderQty - AllocQty
      If OrderQty > 0 Then
        a(i, 2) = OrderQty
        i = i - 1
      End If
    Next i
    .Columns(10).Resize(, 2).Value = b
  End With
End Sub
 
Upvote 0
Solution
Peter_SSs said:
Thanks.
Not sure that I have it all covered, but try code below.

If it fails, please give specific sample data, expected results and explanation.

VBA Code: 
Sub QtyAllocation_v2()
  Dim d As Object
  Dim a As Variant, b As Variant, Mat As Variant
  Dim i As Long, OrderQty As Variant, AvailQty As Long
  Dim AllocQty As String
 
  With Sheets("Sheet2")
    a = Application.Index(.Range("A1").CurrentRegion.Value, Evaluate("row(2:" & .Range("A1").End(xlDown).Row & ")"), Array(1, 3, 4))
  End With
  Set d = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a)
    d(a(i, 1)) = d(a(i, 1)) & "|" & a(i, 2) & "#" & a(i, 3)
  Next i
  With Sheets("Order Sheet").ListObjects(1).DataBodyRange
    a = Application.Index(.Value, Evaluate("row(1:" & .Rows.Count & ")"), Array(7, 9))
    ReDim b(1 To UBound(a), 1 To 2)
    For i = 1 To UBound(a)
      Mat = a(i, 1)
      If Len(d(Mat)) > 0 Then
        AvailQty = Val(Mid(Split(d(Mat), "#")(0), 2))
      Else
        AvailQty = OrderQty
        d(Mat) = "|#Will share ETA shortly"
      End If
      OrderQty = a(i, 2)
      AllocQty = IIf(AvailQty < OrderQty, AvailQty, OrderQty)
      b(i, 1) = IIf(Len(b(i, 1)) > 0, b(i, 1) & "|" & AllocQty, Val(AllocQty))
      AvailQty = AvailQty - AllocQty
      b(i, 2) = IIf(Len(b(i, 2)) > 0, b(i, 2) & "|", "") & Split(Split(d(Mat), "|")(1), "#")(1)
      If AvailQty > 0 Then
        d(Mat) = "|" & AvailQty & Mid(d(Mat), InStr(1, d(Mat), "#"))
      Else
        d(Mat) = Mid(d(Mat), InStr(2, d(Mat) & "|", "|"))
      End If
      OrderQty = OrderQty - AllocQty
      If OrderQty > 0 Then
        a(i, 2) = OrderQty
        i = i - 1
      End If
    Next i
    .Columns(10).Resize(, 2).Value = b
  End With
End Sub
Click to expand...
Thanks Peter,

Its working absolutely fine , Thanks a lot from bottom of my heart!!!

God Bless you.

Sanket
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Locked
  • Question
Find link between which order supplies which demand!!
Replies
1
Views
256
Joe4
Joe4
A
  • Solved
Replace formula with zero when populate Right mark for two sheets
Replies
3
Views
188
awoohaw
awoohaw
M
  • Question
Auto adjust row heights based on specific column
Replies
1
Views
159
XLfriend777
XLfriend777
S
  • Solved
Find Date at Which Open Orders will Exhaust Finished Goods on Hand
Replies
3
Views
531
Eric W
Eric W
K
  • Question
populate amounts for column debit,credit for each customer from file to another
Replies
0
Views
206
KalilMe
K

Forum statistics

Threads
1,214,832
Messages
6,121,849
Members
449,051
Latest member
excelquestion515

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top