# Thread: Compare values in 2 tables

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

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

Ben

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

4. Hi Michael

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

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. 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. 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```

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. Jaslake,

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

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
•