Results 1 to 10 of 10

Thread: Compare values in 2 tables

  1. #1

    Compare values in 2 tables



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I have the attached spreadsheet. I have 2 tables on the same sheet. What I need is to compare the quantities (LOC QTY) between the 2 tables. If the quantites are the same, I will have a "Yes" in Column Z otherwise, I will have a "No".

    Thanks in advance for your help
    Attached Files Attached Files

  2. #2
    You can try this in Z2
    =if(vlookup(k2,a:f,6,false)=f2,"Yes","No")

    Ben

  3. #3
    Thanks Ben but what the VBA code should be?

  4. #4
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    Hi Michael

    Is it normal that you'll have duplicate NIIN Numbers in Columns A and K?

    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Dups.jpg 
Views:	26 
Size:	72.5 KB 
ID:	3453  
    John

  5. #5
    yes Jaslake. It is normal to have duplicate NIIN. It is why I have created the UniqueID which is the concatenation of the NIIN number and the stowage number.

  6. #6
    You can put this at the end of your code

    Set mysht = ThisWorkbook.Sheets("Sheet1")
    zEnd = Application.Max(mysht.Cells(65536, 12).End(xlUp).Row, 2)


    For i = 2 To zEnd
    myValTB2 = mysht.Cells(i, 12)
    myQtyTB2 = mysht.Cells(i, 18)

    If IsError(Application.VLookup(myValTB2, Table1, 1, False)) = False Then
    myQtyTB1 = Application.VLookup(myValTB2, Table1, 7, False)
    If myQtyTB1 = myQtyTB2 Then
    myQtyDiff = "Yes"
    Else
    myQtyDiff = "No"
    End If
    Else
    myQtyDiff = "Not Found"
    End If
    mysht.Cells(i, 21).Value = myQtyDiff
    Next

    Set mysht = Nothing

    Ben

  7. #7
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    Hi Michael

    I have no idea what your Sub LookUp() Code was doing so I hijacked it a bit.

    Try this Code in a General Module...CTRL + x will fire the Code.
    Code:
    Option Explicit
    Dim ws              As Worksheet
    Dim Rng             As Range
    Dim cel             As Range
    Dim c               As Range
    Dim LR              As Long
    
    Sub LookUp()
       'put all the variable definitions at top for easy reference
    
       Application.ScreenUpdating = False
       Set ws = Sheets("Sheet1")
       With ws
          .Columns(26).ClearContents
          .Range("A1").EntireColumn.Insert
          .Range("A1").ColumnWidth = 17.43
          .Range("L1").EntireColumn.Insert
          .Range("L1").ColumnWidth = 17.43
          .Range("A1").Value = "UniqueID"
          .Range("L1").Value = "UniqueID"
    
          LR = .Cells(.Rows.Count, "B").End(xlUp).Row
          .Range("A2:A" & LR).Formula = "=B2&""-""&E2"
          LR = .Cells(.Rows.Count, "M").End(xlUp).Row
          .Range("L2:L" & LR).Formula = "=M2&""-""&P2"
          Call Compare_QTY
          .Columns(12).EntireColumn.Delete
          .Columns(1).EntireColumn.Delete
       End With
       Application.ScreenUpdating = True
    End Sub
    
    
    Sub Compare_QTY()
       '   Application.ScreenUpdating = False
       With ws
          LR = .Range("A" & .Rows.Count).End(xlUp).Row
          Set Rng = .Range("A2:A" & LR)
          For Each cel In Rng
             Set c = .Columns(12).Find(cel.Value, , xlValues, xlWhole, xlByRows, xlNext, False)
             If Not c Is Nothing Then
                If cel.Offset(0, 6).Value = c.Offset(0, 6).Value Then
                   .Cells(cel.Row, "AB").Value = "Yes"
                Else
                   .Cells(cel.Row, "AB").Value = "No"
                End If
             End If
          Next cel
       End With
       '   Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    John

  8. #8
    Thanks a lot BenoitP. I will try it and will let you know if it works. By the way Are you French? I am just curious due to the name Benoit.

  9. #9
    Jaslake,

    Thanks a lot. I will integrate this with what I have and will let you know if it works. Great thanks!!

  10. #10
    Yes, French Canadian, from Montréal.

    The "find" method from John is also very good.
    I use that a lot.

    Thats's your choice to use the "vlookup" or the "find" method.

    In the code: I put the "Not found" label because some of your data from Table2 could not be found in Table1.

    Ben

Posting Permissions

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