Need MACRO for accounting of VOI consumption (FIFO)

[supladaiii]

Hello. I have (2) tables. PartNo is alphanumeric.

TABLE1 data is from Vendor. This is the shipment register.
Assume that each shipment/PackList is for a single PartNo but multiple quantities.
Columns: PartNo, ShipDate, ShipQty, PO, PackList.

PartNo

12345-A

45678-B

12345-A

45678-B

<tbody>
[TD="width: 68"]ShipDate[/TD]
[TD="width: 64"]ShipQty[/TD]
[TD="width: 64"]PO[/TD]
[TD="width: 64"]Packlist[/TD]

[TD="class: xl65, align: right"]01/31/19[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]90100[/TD]
[TD="align: right"]50001[/TD]

[TD="class: xl65, align: right"]01/31/19[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]90100[/TD]
[TD="align: right"]50002[/TD]

[TD="class: xl65, align: right"]02/15/19[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]90200[/TD]
[TD="align: right"]50003[/TD]

[TD="class: xl65, align: right"]02/28/19[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]90200[/TD]
[TD="align: right"]50004[/TD]

</tbody>

TABLE2 data is from Customer. This is the customer's consumption report that identifies which PartNo was pulled when.
Assume that UseQty is either 1 or 2 and MatlDoc+MatlDoc_LineNo is a unique identifier.
Columns: PartNo, PullDate, UseQty, MatlDoc, MatlDoc_LineNo, PymtDoc.

PartNo
12345-A
12345-A
45678-B
12345-A
12345-A
45678-B		50002
12345-A		50003
45678-B		50002
45678-B		50004
45678-B		50004
45678-B		50004

<tbody>
[TD="class: xl65, width: 64"]PullDate[/TD]
[TD="width: 64"]UseQty[/TD]
[TD="width: 64"]MatlDoc[/TD]
[TD="width: 64"]MatlDoc_LineNo[/TD]
[TD="width: 64"]PymtDoc[/TD]
[TD="class: xl66, width: 64"]PackList[/TD]
[TD="width: 64"][/TD]

[TD="class: xl65, align: right"]02/01/19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]887654[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl66, align: right"]50001[/TD]

[TD="class: xl65, align: right"]02/01/19[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]887654[/TD]
[TD="align: right"]2[/TD]

[TD="class: xl66, align: right"]50001[/TD]

[TD="class: xl65, align: right"]02/01/19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]887654[/TD]
[TD="align: right"]3[/TD]

[TD="class: xl66, align: right"]50002[/TD]

[TD="class: xl65, align: right"]02/15/19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]887785[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl66, align: right"]50001[/TD]

[TD="class: xl65, align: right"]02/15/19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]887785[/TD]
[TD="align: right"]2[/TD]

[TD="class: xl66, align: right"]50003[/TD]

[TD="class: xl65, align: right"]02/15/19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]887785[/TD]
[TD="align: right"]3[/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]02/20/19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]889100[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]02/20/19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]889100[/TD]
[TD="align: right"]2[/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]02/28/19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]890150[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]03/01/19[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]890175[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]03/04/19[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]890250[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl66"][/TD]

</tbody>

GOAL: If info is blank then Assign/Identify PackList# for each consumption. (I have added a column [PackList] in TABLE2 for this info)

I need a MACRO that would "pull" consumption from a PackList number and assign the PackList from oldest to newest (FIFO).

{At the moment, I am currently spending way too much time doing this manually even with column helpers and conditional formatting to let me know which packlist is completely used.}

 

[Trebor76]

Hi supladaiii,

I may have misunderstood your request (very likely) but can't you just a VLOOKUP in TABLE2 to return the PackList i.e.

=VLOOKUP([@PartNo],Table1[#All],5,FALSE)

Robert

 

[supladaiii]

I need to assign the next available packlist# for the corresponding PartNo pulled/used.

e.g. -PackList#50002 is a shipment consisting 3pcs of PartNo# 45678-B. [see TABLE1]. 1pc was used/pulled on 2/01/2019 for MaterialDoc#887654. The next 2pcs of consumption for PartNo#45678-B will be assigned to complete PackList#50002 after which, system will start pulling from PackList#50004.

Thanks!

 

[LMacD]

Try this macro.
Both tables are on the same sheet and this marco is in a module on that sheet
A column is added to table1 heading "UseQty"
It assumes the Table1 is sort by packlist numerically
The consumption quantity in any 1 row is not higher than the unused quanity in any packlist. If that assumption is wrong more logic to split consumption between two packlists is needed

[FONT=Verdana]Option Explicit[/FONT]
[FONT=Verdana]Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Dim iLoop
    Dim iShp As Integer
    Dim iUse As Integer
    Dim Table2Row As Integer
    
    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("Table2[UseQty]")[/FONT]
[FONT=Verdana]    'Is the changed cell in the target range
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
        Table2Row = Target.Row - 1
       
        For iLoop = 1 To Range("Table1[#Data]").Rows.Count
             iShp = Range("Table1[ShipQty]")(iLoop)
             iUse = Range("Table1[UseQty]")(iLoop)
             If iShp - iUse > 0 And _
                 Range("Table1[PartNo]")(iLoop) = Range("Table2[PartNo]")(Table2Row) Then
                 
                 Range("Table1[UseQty]")(iLoop) = Range("Table1[UseQty]")(iLoop) + _
                        Range("Table2[UseQty]")(Table2Row)
                 
                 Range("Table2[PackList]")(Table2Row) = Range("Table1[PackList]")(iLoop)
                 Exit Sub
             End If
         Next iLoop
     End If
End Sub
[/FONT]

 

[supladaiii]

Thank you for the macro however I can't seem to make it show up in the macro list? I can see the module when I view the code but it does not show up when I try to run sub user form. Is there anything I was supposed to customize or change in the macro?

**Used your parameters: both tables in the same sheet, added a column in table1 for [UseQty].

 

[LMacD]

I tweaked the macro so it does a sort on tghe packlist. I also added logic to ensure the qty is not exceed the used amount a messge if it could not find a Part# to fill the order.

Likely you put the macro in the user for. It needs to go in the worksheet.

Open the workbook
expand Microsoft Excel Objects
Double Click on Sheet1 (The worksheet that your two tables are on)
Copy and paste the macro below into that worksheet space.
When the worksheet changes it will trigger the macro.
If you did put the original marco in the user form go to the module in the user form and delete it.

I assume your user form add a row to table 1 and fills in at least the partnumber and the quantity used.

Also look at the order the user for fills in the data in table 2. It must fill in the part number first and the qtyused second. The macro does not care when the other data in the table 2 row is filled in. The macro does not use that information.



[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim iLoop
Dim iShp As Integer
Dim iUse As Integer
Dim Table2Row As Integer




' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("Table2[UseQty]")
'Is the changed cell in the target range
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Clear
ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Add _
Key:=Range("Table1[[#All],[Packlist]]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortTextAsNumbers
With ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Table2Row = Target.Row - 1

For iLoop = 1 To Range("Table1[#Data]").Rows.Count
iShp = Range("Table1[ShipQty]")(iLoop)
iUse = Range("Table1[UseQty]")(iLoop)
If iShp - iUse - Range("Table2[UseQty]")(Table2Row) >= 0 And _
Range("Table1[PartNo]")(iLoop) = Range("Table2[PartNo]")(Table2Row) Then

Range("Table1[UseQty]")(iLoop) = Range("Table1[UseQty]")(iLoop) + _
Range("Table2[UseQty]")(Table2Row)

Range("Table2[PackList]")(Table2Row) = Range("Table1[PackList]")(iLoop)
Exit Sub
End If
Next iLoop
'If I did not find a row in table 1 I would get here
MsgBox "No packlists had sufficient unsed quantity to fill the order for " & Range("Table2[PartNo]")(Table2Row)
End If
End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][/FONT]