Two Sheets Two Column Comparing

ayazgreat

Well-known Member
Joined
Jan 19, 2008
Messages
1,151
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

ABCD
1Inclusive all repeated values in both sheets Inclusive all repeated values which are not in sheet1Inclusive all repeated values which are not in sheet2
2Values in Sheet1 & Sheet2 Value Not in Sheet1Value Not in Sheet2
31345 58974789
41356 1345
51234 4789
61345 1234
71652
84789
91345
101789

<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
1List A
21345
31356
44789
51234
61345
71652
84789
91345
101789

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

Sheet2

A
1List B
21345
31356
45897
51234
61345
71652
84789
91345
101789
111345
124789
131234

<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
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 19, 2008
Messages
1,151
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

EFGHIJKL
1 Comparing Result after running code
2 Sheet1Sheet2 In BothNot in Sheet1Not in Sheet2
3 13451345 134558974789
4 13561356 13561345
5Not in sheet247895897Not in sheet1 12344789
6 12341234 13451234
7 13451345 1652
8 16521652 4789
9 47894789 1345
10 13451345 1789
11 17891789
12 1345Not in sheet1
13 4789Not in sheet1
14 1234Not in sheet1

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 19, 2008
Messages
1,151

ADVERTISEMENT

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



EFGHIJKL
1 Comparing Result after running code
2 Sheet1Sheet2 In BothNot in Sheet1Not in Sheet2
3 13451345 134558977879
4 13561356 13561345
5Not in sheet278795897Not in sheet1 12344789
6 12341234 13451234
7 13451345 1652
8 16521652 4789
9 47894789 1345
10 13451345 1789
11 17891789
12 1345Not in sheet1
13 4789Not in sheet1
14 1234Not 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
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
Are you trying to compare the differences between the 2 lists, or the differences between the individual rows?
 

ayazgreat

Well-known Member
Joined
Jan 19, 2008
Messages
1,151

ADVERTISEMENT

Comparing the differences between the 2 lists
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
In that case I still don't understand, how can 4789 (which is in both lists) be "Not in Sheet1"?
 

ayazgreat

Well-known Member
Joined
Jan 19, 2008
Messages
1,151
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
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
Ok, I understand now, but not sure I can help.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top