# Thread: Compare values in 2 tables

1. ## Compare values in 2 tables

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   Reply With Quote

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

Ben  Reply With Quote

3. Thanks Ben but what the VBA code should be?  Reply With Quote

4. Hi Michael

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

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.  Reply With Quote

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
End If
mysht.Cells(i, 21).Value = myQtyDiff
Next

Set mysht = Nothing

Ben  Reply With Quote

7. 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```  Reply With Quote

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.  Reply With Quote

9. Jaslake,

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

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  Reply With Quote

#### Posting Permissions

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