Results 1 to 5 of 5

Thread: Sequence filling of table for order picking based on current inventory

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    The answer needed:
    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. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default 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. #3
    New Member
    Join Date
    Apr 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    The right answer:
    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. #4
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default 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. #5
    New Member
    Join Date
    Apr 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •