Sequence filling of table for order picking based on current inventory

K

k3nz3n

New Member
Joined
Apr 20, 2017
Messages
3
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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Order Number[/TD]
[TD]Fruit Ordered[/TD]
[TD]Quantity Ordered[/TD]
[TD]To Pack from Current Inventory[/TD]
[TD]Wait for Stock to Arrive[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Apples[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Oranges[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Kiwi[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1005[/TD]
[TD]Apples[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1005[/TD]
[TD]Oranges[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1008[/TD]
[TD]Apples[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Current Inventory:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Current Inventory[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

The answer needed:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Order Number[/TD]
[TD]Fruit Ordered[/TD]
[TD]Quantity Ordered[/TD]
[TD]To Pack from Inventory[/TD]
[TD]Wait for Stock to Arrive[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Apples[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Oranges[/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Kiwi[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1005[/TD]
[TD]Apples[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1005[/TD]
[TD]Oranges[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1008[/TD]
[TD]Apples[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

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

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Try this:-
"Current Inventory in sheet2, starting "A1"
"Order form" in sheet 1 Starting "A1"

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

[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("B2", Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] .Item(Dn.Value) >= Dn.Offset(, 1).Value [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, 2) = Dn.Offset(, 1)
            Dn.Offset(, 3) = 0
            .Item(Dn.Value) = .Item(Dn.Value) - Dn.Offset(, 1).Value
        [COLOR="Navy"]ElseIf[/COLOR] .Item(Dn.Value) < Dn.Offset(, 1).Value [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, 2) = .Item(Dn.Value)
            Dn.Offset(, 3) = Dn.Offset(, 1) - Dn.Offset(, 2)
           .Item(Dn.Value) = 0
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Order Number[/TD]
[TD]Fruit Ordered[/TD]
[TD]UOM[/TD]
[TD]Ordered Quantity[/TD]
[TD]To Pack from Current Inventory[/TD]
[TD]Wait for Stock to Arrive[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Apples[/TD]
[TD]Pc[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Oranges[/TD]
[TD]Pc[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Kiwi[/TD]
[TD]Pc[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1005[/TD]
[TD]Apples[/TD]
[TD]Pc[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1005[/TD]
[TD]Oranges[/TD]
[TD]Pc[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1008[/TD]
[TD]Apples[/TD]
[TD]Case[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1010[/TD]
[TD]Oranges[/TD]
[TD]Case[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1012[/TD]
[TD]Kiwi[/TD]
[TD]Case[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Current Inventory:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]UOM[/TD]
[TD]Current Inventory[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]Pc[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]Case[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]Pc[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]Case[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD]Pc[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD]Case[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

The right answer:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Order Number[/TD]
[TD]Fruit Ordered[/TD]
[TD]UOM[/TD]
[TD]Ordered Quantity[/TD]
[TD]To Pack from Current Inventory[/TD]
[TD]Wait for Stock to Arrive[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Apples[/TD]
[TD]Pc[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Oranges[/TD]
[TD]Pc[/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Kiwi[/TD]
[TD]Pc[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1005[/TD]
[TD]Apples[/TD]
[TD]Pc[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1005[/TD]
[TD]Oranges[/TD]
[TD]Pc[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1008[/TD]
[TD]Apples[/TD]
[TD]Case[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1010[/TD]
[TD]Oranges[/TD]
[TD]Case[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1012[/TD]
[TD]Kiwi[/TD]
[TD]Case[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG13Jun18
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Txt = Dn.Value & "," & Dn.Offset(, 1).Value
    .Item(Txt) = Dn.Offset(, 2).Value
[COLOR="Navy"]Next[/COLOR]

[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("B2", Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
     Txt = Dn.Value & "," & Dn.Offset(, 1).Value
    [COLOR="Navy"]If[/COLOR] .exists(Txt) [COLOR="Navy"]Then[/COLOR]
        
        [COLOR="Navy"]If[/COLOR] .Item(Txt) >= Dn.Offset(, 2).Value [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, 3) = Dn.Offset(, 2)
            Dn.Offset(, 4) = 0
            .Item(Txt) = .Item(Txt) - Dn.Offset(, 2).Value
        [COLOR="Navy"]ElseIf[/COLOR] .Item(Txt) < Dn.Offset(, 2).Value [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, 3) = .Item(Txt)
            Dn.Offset(, 4) = Dn.Offset(, 2) - Dn.Offset(, 3)
           .Item(Txt) = 0
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
You must log in or register to reply here.

Similar threads

ExcelNewbie2020
  • Question Question
lookup and distribute the qty based on capacity
Replies
1
Views
271
bobaol
B
I
  • Question Question
Cascading Drop Downs
Replies
3
Views
271
imran999
I
J
  • Question Question
Lookup a Column Range by Matching Heading
Replies
3
Views
405
jcoeng
J
C
  • Question Question
Stock inventory - count unique sequences from barcode
Replies
0
Views
574
ChemistryBMS
C
S
  • Question Question
VBA Inventory UserForm ID generation
Replies
1
Views
997
Herakles
H

Forum statistics

Threads
1,224,816
Messages
6,181,139
Members
453,021
Latest member
Justyna P

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