Multiple criteria search

[sock]

Hi All,

I have two worksheets in a workbook (Sheet1& Sheet2)

Need to validate the Invoice number& invoice amount and received quantity.

In sheet1 Invoice amount in J column, Invoice number in L Column Received quantity is in S column.

In sheet2 Invoice amount in K column, Invoice number in R Column Received quantity is in S column.

comments need to be updated in Sheet2 V column. Comments should be “Invoice Number match” or “Invoice number/ Invoice amount match”.

Base value is invoice number. If invoice amount match with another sheet then we can validate the Invoice number and amount, else we can update the comments like mismatch.

This same validation for 50 workbooks with different names.

Any help??

Thanks
Jack

 

[Mississippi Girl]

=IFNA(IF((SUMIFS(Sheet1!J:J,Sheet1!L:L,Sheet2!R6,Sheet1!S:S,Sheet2!S:S)-Sheet2!K6=0),"Invoice number/Invoice amount match",IF(MATCH(R6,Sheet1!L:L,0),"Invoice Number match")),"Invoice not on sheet1")

This looks at all the invoice numbers and amounts in sheet1 and if the invoice number matches, then it subtracts the invoice amount from the invoice amount on sheet 2; if that equals zero, then Invoice number/Invoice amount match is displayed. If it does not equal zero, then it looks to see if the invoice number is listed on sheet1, if so then it's an Invoice Number match. If the invoice number is listed on sheet2 but not on sheet1, then you get Invoice not on sheet1.

 

[sock]

Thanks for your reply... can we do that in using macro code??

 

[Mississippi Girl]

This is something that JoeMo posted about 9 years ago, but it works.

The code below assumes that you will have data in column A on sheet2 and that the first row of data in both sheets 1 and 2 will begin on Row 2 and that the first cell in which the formula is to be placed is V2.

Sub FillDown()
Range("V2").Formula = "=IFNA(IF((SUMIFS(Sheet1!J:J,Sheet1!L:L,Sheet2!R2,Sheet1!S:S,Sheet2!S:S)-Sheet2!K2=0),""Invoice number/Invoice amount match"",IF(MATCH(R2,Sheet1!L:L,0),""Invoice Number match"")),""Invoice not on sheet1"")"
Range("V2", "V" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
End Sub

 

[sock]

Thanks for your code... its working for just 1 file.. I need to do the same thing for 60 workbooks.

any help...

Thanks
Jack

 

[Mississippi Girl]

I don't know of a way to insert the function in to multiple files. I think you will have to manually add it to each file. That would be a one time add though; after that you should be fine.

 

[sock]

I got the below code but it will not work for duplicate values in Sheet2 any help..

Sub Update1()
    Dim w1 As Worksheet, w2 As Worksheet
    Dim c As Range, FR As Long
    Application.ScreenUpdating = False
    Set w1 = Sheets("Sheet1")
    Set w2 = Sheets("Sheet2")
    For Each c In w1.Range("A2", w1.Range("A" & Rows.Count).End(xlUp))
        FR = 0
        On Error Resume Next
        FR = Application.Match(c, w2.Columns("A"), 0)
        On Error GoTo 0
        If FR <> 0 Then w2.Range("D" & FR).Value = "Matching"
    Next c
    Application.ScreenUpdating = True
End Sub

regards
Jack