Sequence filling of table for order picking based on current inventory

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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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

Forum statistics

Threads
1,223,888
Messages
6,175,205
Members
452,618
Latest member
Tam84

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