Bring enough lines to fulfill number

[Razr]

Hello everyone i got the following the question,

I have an excel worksheet that contains many lines of products and quantity that i want to return to producers with the following format:

SKU | Title | Qnty to Return | Producer code
131451 | Apple Box | 8 | 2000151
131291 | Brown Desk Le Grand | 14 | 2000043

and another worksheet that contains all the invoices that i have in my system for a period of time in the following format
SKU | Title | Qnty Received| Producer code | Invoice Number
131451 | Apple Box | 3 | 2000151 | SLN124115
131451 | Apple Box | 5 | 2000151 | SLN124145
131451 | Apple Box | 2 | 2000151 | SLN124212
131451 | Apple Box | 5 | 2000151 | SLN124611
131291 | Brown Desk Le Grand | 2 | 2000153 | SLN124511
131291 | Brown Desk Le Grand | 1 | 2000154 | SLN123195

so what i want to do is have a 4th column in my worksheet with the products that i want to return that will be getting enough invoices to justify the return quantity for example for the product Apple Box would be:
131451 | Apple Box | 8 | 2000151 | SLN124145 SLN124115

We don't mind if the invoice number corresponds to a larger quantity than the quantity that i want to retun but should return enough invoices to justify at least the quantity that i want to return.
We also dont mind if it has to return more lines instead of populating the results in one cell

I hope you understand what i mean

If this is not possible through excel could may be done with access? if yes how?

Thanks in advance

 

[syntaxed]

Assuming your first worksheet is Sheet1 and your second worksheet is Sheet2 and the layout of Sheet2 is exactly as you have shown, then you may use the following function:

Function Invoice(SKU As Range, Qty As Range)
    Dim RunTotal, Inv As String
    
    For mycounter = 1 To Sheet2.UsedRange.Rows.Count
        If Sheet2.Cells(mycounter, 1).Value = SKU.Value Then
            RunTotal = RunTotal + Sheet2.Cells(mycounter, 3).Value
            If Inv = "" Then
                Inv = Sheet2.Cells(mycounter, 5).Value
            Else
                Inv = Inv & " " & Sheet2.Cells(mycounter, 5).Value
            End If
            If RunTotal >= Qty.Value Then
                Exit For
            End If
        End If
    Next
    
    If RunTotal < Qty.Value Then
        Inv = "Could not find sufficient qnty"
    End If
    
    Invoice = Inv
End Function

For your example, in the 5th column (column in which you want to display the invoices), you would enter this in the Apple Boxes row (row 2)
=invoice(A2,C2)

where A2 is the cell that contains the SKU code for the item you are looking for, and C2 contains the quantity desired.

 

[Razr]

Assuming your first worksheet is Sheet1 and your second worksheet is Sheet2 and the layout of Sheet2 is exactly as you have shown, then you may use the following function:

Function Invoice(SKU As Range, Qty As Range)
    Dim RunTotal, Inv As String
    
    For mycounter = 1 To Sheet2.UsedRange.Rows.Count
        If Sheet2.Cells(mycounter, 1).Value = SKU.Value Then
            RunTotal = RunTotal + Sheet2.Cells(mycounter, 3).Value
            If Inv = "" Then
                Inv = Sheet2.Cells(mycounter, 5).Value
            Else
                Inv = Inv & " " & Sheet2.Cells(mycounter, 5).Value
            End If
            If RunTotal >= Qty.Value Then
                Exit For
            End If
        End If
    Next
    
    If RunTotal < Qty.Value Then
        Inv = "Could not find sufficient qnty"
    End If
    
    Invoice = Inv
End Function

For your example, in the 5th column (column in which you want to display the invoices), you would enter this in the Apple Boxes row (row 2)
=invoice(A2,C2)

where A2 is the cell that contains the SKU code for the item you are looking for, and C2 contains the quantity desired.

Thank you very much! this really worked :D

One last question is it possible instead of saying "Couldnt find sufficient qnty " to post the total qnty allowed i.e. if i want to return 4 but i only have 3 as a total of invoices could it simply say 3 instead of "Couldnt find sufficient qnty "

Thanks in advance

 

[Razr]

Thank you very much! this really worked :D

One last question is it possible instead of saying "Couldnt find sufficient qnty " to post the total qnty allowed i.e. if i want to return 4 but i only have 3 as a total of invoices could it simply say 3 instead of "Couldnt find sufficient qnty "

Thanks in advance

Oh and one more question, since sheet 2 may contain more than one line with the same SKU and invoice is it possible to have them returned on sheet 1 only once so they don't get repeated in the same cell ? (this is regardless of the calculation)

 

[Razr]

So I guess no lack on this one ?

Anyway, thanks for your help the biggest part of the problem was solved.

 

[syntaxed]

Hey Razr,

Sorry I didn't see your posts until today. Try this:

Function Invoice(SKU As Range, Qty As Range)
    Dim RunTotal, invCounter, invCount, myRowCounter
    Dim Inv() As String
    Dim myFlag As Boolean
 
    ReDim Inv(1 To Sheet2.UsedRange.Rows.Count)
 
    invCounter = 1
    myFlag = False
 
    For myRowCounter = 1 To Sheet2.UsedRange.Rows.Count
        If Sheet2.Cells(myRowCounter, 1).Value = SKU.Value Then
            RunTotal = RunTotal + Sheet2.Cells(myRowCounter, 3).Value
            For invCount = 1 To invCounter
                If Inv(invCount) = Sheet2.Cells(myRowCounter, 5).Value Then
                    myFlag = True
                    Exit For
                End If
            Next
            If myFlag = False Then
                Inv(invCounter) = Sheet2.Cells(myRowCounter, 5).Value
                invCounter = invCounter + 1
            End If
            If RunTotal >= Qty.Value Then
                Exit For
            End If
        End If
        myFlag = False
    Next
    If RunTotal < Qty.Value Then
        For invCount = 1 To invCounter
            Inv(invCount) = ""
        Next
        Inv(1) = RunTotal
        invCounter = 1
    End If
 
    For invCount = 1 To invCounter
        If invCount = 1 Then
            Invoice = Invoice & Inv(invCount)
        Else
            Invoice = Invoice & " " & Inv(invCount)
        End If
    Next
End Function

I haven't had a chance to test it, so if there are any problems let me know.