# Thread: Sequence filling of table for order picking based on current inventory Thanks:  2 Post #5293127 (1)Post #5293661 (1) Likes:  2 Post #5293127 (1)Post #5293661 (1)

1. ## Sequence filling of table for order picking based on current inventory

Hi all, I am trying to find the best way to fill up a dynamic table with the right values as easily as possible (hopefully one click) once I input the the order number and it's details.

I am stumped not sure whether I'll need to use helper columns / vba.

I wanted to get the answers to the "To Pack From Inventory" and "To Wait For Stock To Arrive" column.

 Order Number Fruit Ordered Quantity Ordered To Pack from Current Inventory Wait for Stock to Arrive 1001 Apples 7 1001 Oranges 7 1001 Kiwi 5 1005 Apples 5 1005 Oranges 5 1008 Apples 3

Current Inventory:
 Fruit Current Inventory Apples 10 Oranges 5 Kiwi 5

 Order Number Fruit Ordered Quantity Ordered To Pack from Inventory Wait for Stock to Arrive 1001 Apples 7 7 0 1001 Oranges 7 5 2 1001 Kiwi 5 5 0 1005 Apples 5 3 2 1005 Oranges 5 0 5 1008 Apples 3 0 3

What would be the most efficient and easiest way to achieve this? Thank you all in advanced for any thoughts on this!

2. ## Re: Sequence filling of table for order picking based on current inventory

Try this:-
"Current Inventory in sheet2, starting "A1"
"Order form" in sheet 1 Starting "A1"

Results column "D & E" of sheet1.
Code:
```Sub MG12Jun30
Dim Rng As Range, Dn As Range, n As Long
With Sheets("Sheet2")
Set Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
.Item(Dn.Value) = Dn.Offset(, 1).Value
Next

With Sheets("Sheet1")
Set Rng = .Range("B2", Range("B" & Rows.Count).End(xlUp))
End With

For Each Dn In Rng
If .exists(Dn.Value) Then
If .Item(Dn.Value) >= Dn.Offset(, 1).Value Then
Dn.Offset(, 2) = Dn.Offset(, 1)
Dn.Offset(, 3) = 0
.Item(Dn.Value) = .Item(Dn.Value) - Dn.Offset(, 1).Value
ElseIf .Item(Dn.Value) < Dn.Offset(, 1).Value Then
Dn.Offset(, 2) = .Item(Dn.Value)
Dn.Offset(, 3) = Dn.Offset(, 1) - Dn.Offset(, 2)
.Item(Dn.Value) = 0
End If
End If
Next
End With
End Sub```
Regards Mick

3. ## Re: Sequence filling of table for order picking based on current inventory

MickG your answer was spot on. This serves exactly it's function. Really awesome!

I wanted to add one more column to compare which is the UOM. I thought I could play around with the code to compare between one more criteria (UOM), however I am such a amateur in VBA and couldn't do it.

I have modified the table below on what is needed to be compared. So sorry I did not add this criteria in the initial question. Hope will be able to get some help on this. Thank you in advanced!

Order Sheet:
 Order Number Fruit Ordered UOM Ordered Quantity To Pack from Current Inventory Wait for Stock to Arrive 1001 Apples Pc 7 1001 Oranges Pc 7 1001 Kiwi Pc 5 1005 Apples Pc 5 1005 Oranges Pc 5 1008 Apples Case 3 1010 Oranges Case 2 1012 Kiwi Case 2

Current Inventory:
 Fruit UOM Current Inventory Apples Pc 10 Apples Case 2 Oranges Pc 5 Oranges Case 0 Kiwi Pc 5 Kiwi Case 6

 Order Number Fruit Ordered UOM Ordered Quantity To Pack from Current Inventory Wait for Stock to Arrive 1001 Apples Pc 7 7 0 1001 Oranges Pc 7 5 2 1001 Kiwi Pc 5 5 0 1005 Apples Pc 5 3 2 1005 Oranges Pc 5 0 5 1008 Apples Case 3 2 1 1010 Oranges Case 2 0 2 1012 Kiwi Case 2 2 0

4. ## Re: Sequence filling of table for order picking based on current inventory

Try this:-
Code:
```Sub MG13Jun18
Dim Rng As Range, Dn As Range, n As Long, Txt As String
With Sheets("Sheet2")
Set Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
Txt = Dn.Value & "," & Dn.Offset(, 1).Value
.Item(Txt) = Dn.Offset(, 2).Value
Next

With Sheets("Sheet1")
Set Rng = .Range("B2", Range("B" & Rows.Count).End(xlUp))
End With

For Each Dn In Rng
Txt = Dn.Value & "," & Dn.Offset(, 1).Value
If .exists(Txt) Then

If .Item(Txt) >= Dn.Offset(, 2).Value Then
Dn.Offset(, 3) = Dn.Offset(, 2)
Dn.Offset(, 4) = 0
.Item(Txt) = .Item(Txt) - Dn.Offset(, 2).Value
ElseIf .Item(Txt) < Dn.Offset(, 2).Value Then
Dn.Offset(, 3) = .Item(Txt)
Dn.Offset(, 4) = Dn.Offset(, 2) - Dn.Offset(, 3)
.Item(Txt) = 0
End If
End If
Next
End With
End Sub```
Regards Mick

5. ## Re: Sequence filling of table for order picking based on current inventory

Wow. It works perfectly. Thank you so much and have a great day ahead!