BrutalDawg
New Member
- Joined
- Jun 10, 2015
- Messages
- 41
Hello,
I am trying to be able to better identify shortages without as many steps for other users that are not as fluent in excel. Currently, all I do is take my sales order filter down to 3-4 weeks and run a combination script:
Sub CombineRows()
'Updated 20150511
Dim WorkRng As Range
Dim Dic As Variant
Dim arr As Variant
On Error Resume Next
xTitleId = "Stock-INSERT-DATE-HERE"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set Dic = CreateObject("Scripting.Dictionary")
arr = WorkRng.Value
For i = 1 To UBound(arr, 1)
Dic(arr(i, 1)) = Dic(arr(i, 1)) + arr(i, 2)
Next
Application.ScreenUpdating = False
WorkRng.ClearContents
WorkRng.Range("A1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.keys)
WorkRng.Range("B1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.items)
Application.ScreenUpdating = True
End Sub
Then I utilize a simple conditional formatting rule that highlights any item that is less than Stock. This creates a lot of leg work of verifying when the PO will deliver compared to the sales order. I am trying to develop a tool, where I can have the Sales Orders on Sheet1, Stock on Sheet2, and better identification of shortages. For example stock:
<tbody>
</tbody>
Example Sales Orders:
<tbody>
</tbody>
with an example output of:
<tbody>
</tbody>
Is this possible with the SO's having split quantities?
Thanks for any suggestions!
I am trying to be able to better identify shortages without as many steps for other users that are not as fluent in excel. Currently, all I do is take my sales order filter down to 3-4 weeks and run a combination script:
Sub CombineRows()
'Updated 20150511
Dim WorkRng As Range
Dim Dic As Variant
Dim arr As Variant
On Error Resume Next
xTitleId = "Stock-INSERT-DATE-HERE"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set Dic = CreateObject("Scripting.Dictionary")
arr = WorkRng.Value
For i = 1 To UBound(arr, 1)
Dic(arr(i, 1)) = Dic(arr(i, 1)) + arr(i, 2)
Next
Application.ScreenUpdating = False
WorkRng.ClearContents
WorkRng.Range("A1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.keys)
WorkRng.Range("B1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.items)
Application.ScreenUpdating = True
End Sub
Then I utilize a simple conditional formatting rule that highlights any item that is less than Stock. This creates a lot of leg work of verifying when the PO will deliver compared to the sales order. I am trying to develop a tool, where I can have the Sales Orders on Sheet1, Stock on Sheet2, and better identification of shortages. For example stock:
Item Number | Quantity |
1a1 | 200 |
2b2 | 305 |
<tbody>
</tbody>
Example Sales Orders:
Location | PurchaseOrderNumber | Part Number | Timing | Quantity | When |
80 | 0 | 1a1 | Planning | 50 | 11/1/2016 |
72 | 0 | 1a1 | Planning | 50 | 11/1/2016 |
80 | 0 | 2b2 | Planning | 100 | 11/1/2016 |
80 | A437B | 1a1 | Planning | 50 | 11/8/2016 |
72 | A8683B | 1a1 | Planning | 100 | 11/8/2016 |
72 | A893 | 2b2 | Planning | 50 | 11/8/2016 |
65 | 0 | 1a1 | Planning | 75 | 11/15/2016 |
80 | 0 | 2b2 | Planning | 75 | 11/15/2016 |
65 | A437B | 2b2 | Planning | 100 | 11/15/2016 |
80 | A7893 | 2b2 | Planning | 120 | 11/22/2016 |
65 | B7820 | 1a1 | Planning | 100 | 11/22/2016 |
<tbody>
</tbody>
with an example output of:
Location | PurchaseOrderNumber | Part Number | Timing | Quantity | When | Short Quantity |
80 | 0 | 1a1 | Planning | 50 | 11/1/2016 | |
72 | 0 | 1a1 | Planning | 50 | 11/1/2016 | |
80 | 0 | 2b2 | Planning | 100 | 11/1/2016 | |
80 | A437B | 1a1 | Planning | 50 | 11/8/2016 | |
72 | A8683B | 1a1 | Planning | 100 | 11/8/2016 | 50 |
72 | A893 | 2b2 | Planning | 50 | 11/8/2016 | |
65 | 0 | 1a1 | Planning | 75 | 11/15/2016 | 0 |
80 | 0 | 2b2 | Planning | 75 | 11/15/2016 | |
65 | A437B | 2b2 | Planning | 100 | 11/15/2016 | 20 |
80 | A7893 | 2b2 | Planning | 120 | 11/22/2016 | 0 |
65 | B7820 | 1a1 | Planning | 100 | 11/22/2016 | 0 |
<tbody>
</tbody>
Is this possible with the SO's having split quantities?
Thanks for any suggestions!