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.

Order NumberFruit OrderedQuantity OrderedTo Pack from Current InventoryWait for Stock to Arrive
1001Apples7
1001Oranges7
1001Kiwi5
1005Apples5
1005Oranges5
1008Apples3

<tbody>
</tbody>

Current Inventory:
FruitCurrent Inventory
Apples10
Oranges5
Kiwi5

<tbody>
</tbody>

The answer needed:
Order NumberFruit OrderedQuantity OrderedTo Pack from InventoryWait for Stock to Arrive
1001Apples770
1001Oranges752
1001Kiwi550
1005Apples532
1005Oranges505
1008Apples303

<tbody>
</tbody>

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

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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:
Order NumberFruit OrderedUOMOrdered QuantityTo Pack from Current InventoryWait for Stock to Arrive
1001ApplesPc7
1001OrangesPc7
1001KiwiPc5
1005ApplesPc5
1005OrangesPc5
1008ApplesCase3
1010OrangesCase2
1012KiwiCase2

<tbody>
</tbody>

Current Inventory:
FruitUOMCurrent Inventory
ApplesPc10
ApplesCase2
OrangesPc5
OrangesCase0
KiwiPc5
KiwiCase6

<tbody>
</tbody>

The right answer:
Order NumberFruit OrderedUOMOrdered QuantityTo Pack from Current InventoryWait for Stock to Arrive
1001ApplesPc770
1001OrangesPc752
1001KiwiPc550
1005ApplesPc532
1005OrangesPc505
1008ApplesCase321
1010OrangesCase202
1012KiwiCase220

<tbody>
</tbody>
 
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,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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