# Two Sheets Two Column Comparing

#### ayazgreat

##### Well-known Member
Hi All

After searching a lot , I did not find any solution here and on web, Actual I would like to compare sheet1 a col with sheet2 a col and copy result on sheet "Comparing Result" both comparing sheets several duplicate values, I don't want to remove duplicates values when copying result , Please go through below detail of sheet "Comparing Result"

Comparing Result

 A B C D 1 Inclusive all repeated values in both sheets Inclusive all repeated values which are not in sheet1 Inclusive all repeated values which are not in sheet2 2 Values in Sheet1 & Sheet2 Value Not in Sheet1 Value Not in Sheet2 3 1345 5897 4789 4 1356 1345 5 1234 4789 6 1345 1234 7 1652 8 4789 9 1345 10 1789

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 281px;"><col style="width: 64px;"><col style="width: 339px;"><col style="width: 339px;"></colgroup><tbody>
</tbody>

Sheet1

 A 1 List A 2 1345 3 1356 4 4789 5 1234 6 1345 7 1652 8 4789 9 1345 10 1789

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Sheet2

 A 1 List B 2 1345 3 1356 4 5897 5 1234 6 1345 7 1652 8 4789 9 1345 10 1789 11 1345 12 4789 13 1234

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Fluff

##### MrExcel MVP, Moderator
The sample you gave does not make sense, as you have 4789 listed in all three columns of the Comparing result sheet.
That said is this what you need?
Code:
``````Sub CompareCols()

Dim LstA As Variant
Dim LstB As Variant
Dim i As Long
Dim a As Variant

With Sheets("Sheet1")
LstA = Application.Transpose(.Range("A2", .Range("A" & Rows.Count).End(xlUp)))
End With
With Sheets("Sheet2")
LstB = Application.Transpose(.Range("A2", .Range("A" & Rows.Count).End(xlUp)))
End With

With Sheets("Comparing Result")
Range("A1:C1").Value = Array("In Both", "Not in Sheet1", "Not in Sheet")
For i = 1 To UBound(LstB)
a = Application.Match(LstB(i), LstA, 0)
If Not IsError(a) Then
.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = LstB(i)
Else
.Range("B" & Rows.Count).End(xlUp).Offset(1).Value = LstB(i)
End If
a = ""
Next i
For i = 1 To UBound(LstA)
a = Application.Match(LstA(i), LstB, 0)
If IsError(a) Then
.Range("C" & Rows.Count).End(xlUp).Offset(1).Value = LstA(i)
End If
a = ""
Next i
End With
End Sub``````

#### ayazgreat

##### Well-known Member
Dear Fluff
Thanks for your reply, however after running above code, the result of given below is differ in Comparing result sheet, which I shown above in Comparing Result Sheet

Let me explain you in below example, which will clear , kindly see comparing and result

 E F G H I J K L 1 Comparing Result after running code 2 Sheet1 Sheet2 In Both Not in Sheet1 Not in Sheet2 3 1345 1345 1345 5897 4789 4 1356 1356 1356 1345 5 Not in sheet2 4789 5897 Not in sheet1 1234 4789 6 1234 1234 1345 1234 7 1345 1345 1652 8 1652 1652 4789 9 4789 4789 1345 10 1345 1345 1789 11 1789 1789 12 1345 Not in sheet1 13 4789 Not in sheet1 14 1234 Not in sheet1

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Last edited:

#### Fluff

##### MrExcel MVP, Moderator
I'm afraid I don't understand.
You have 4789 in both sheets, yet you output shows it in both sheets, not in sheet1 & also not in sheet2. How can it be all 3?

#### ayazgreat

##### Well-known Member

Yes you are right 4789 is appearing 2 times in both sheets so the result is below when i change a number there now 4789 is appearing 2 times in sheet2 and one time time sheet1, so example result after running macro will be

 E F G H I J K L 1 Comparing Result after running code 2 Sheet1 Sheet2 In Both Not in Sheet1 Not in Sheet2 3 1345 1345 1345 5897 7879 4 1356 1356 1356 1345 5 Not in sheet2 7879 5897 Not in sheet1 1234 4789 6 1234 1234 1345 1234 7 1345 1345 1652 8 1652 1652 4789 9 4789 4789 1345 10 1345 1345 1789 11 1789 1789 12 1345 Not in sheet1 13 4789 Not in sheet1 14 1234 Not in sheet1

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 90px;"><col style="width: 80px;"><col style="width: 64px;"><col style="width: 101px;"><col style="width: 38px;"><col style="width: 166px;"><col style="width: 92px;"><col style="width: 92px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

#### Fluff

##### MrExcel MVP, Moderator
Are you trying to compare the differences between the 2 lists, or the differences between the individual rows?

#### ayazgreat

##### Well-known Member

Comparing the differences between the 2 lists

#### Fluff

##### MrExcel MVP, Moderator
In that case I still don't understand, how can 4789 (which is in both lists) be "Not in Sheet1"?

#### ayazgreat

##### Well-known Member
Sorry, I am comparing each individual number in both list , as 4789 is appearing twice in sheet2 but appearing one time in sheet1

#### Fluff

##### MrExcel MVP, Moderator
Ok, I understand now, but not sure I can help.

Replies
4
Views
204
Replies
5
Views
191
Replies
37
Views
2K
Replies
13
Views
607
Replies
3
Views
323

1,130,129
Messages
5,640,283
Members
417,134
Latest member
fjleroux187

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back