Compare values in 2 tables

M

Michael1974

New member
Joined
Mar 31, 2015
Messages
49
Reaction score
0
Points
0
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
attachment.php
 

Attachments

  • Location Qty Comparaison Before and After.xls
    106 KB · Views: 14
You can try this in Z2
=if(vlookup(k2,a:f,6,false)=f2,"Yes","No")

Ben
 
Hi Michael

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

attachment.php
 

Attachments

  • Dups.jpg
    Dups.jpg
    72.5 KB · Views: 29
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.
 
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
 
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
 

Attachments

  • Location Qty Comparaison Before and After.xls
    112.5 KB · Views: 7
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.
 
Jaslake,

Thanks a lot. I will integrate this with what I have and will let you know if it works. Great thanks!!
 
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
 
You must log in or register to reply here.
Back
Top