Two Sheets Two Column Comparing

A

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

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]Inclusive all repeated values in both sheets[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]Inclusive all repeated values which are not in sheet1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]Inclusive all repeated values which are not in sheet2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: center"]Values in Sheet1 & Sheet2[/TD]

[TD="align: center"]Value Not in Sheet1[/TD]
[TD="align: center"]Value Not in Sheet2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]1345[/TD]

[TD="align: center"]5897[/TD]
[TD="align: center"]4789[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]1356[/TD]

[TD="align: center"]1345[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: center"]1234[/TD]

[TD="align: center"]4789[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]1345[/TD]

[TD="align: center"]1234[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: center"]1652[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: center"]4789[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]1345[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]
[TD="align: center"]1789[/TD]

</tbody>

Sheet1

A
List A

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: right"]1345[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: right"]1356[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]4789[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: right"]1234[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: right"]1345[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: right"]1652[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: right"]4789[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="align: right"]1345[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]
[TD="align: right"]1789[/TD]

</tbody>

Sheet2

A
List B

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: right"]1345[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: right"]1356[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]5897[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: right"]1234[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: right"]1345[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: right"]1652[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: right"]4789[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="align: right"]1345[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]
[TD="align: right"]1789[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]
[TD="align: right"]1345[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]12[/TD]
[TD="align: right"]4789[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]13[/TD]
[TD="align: right"]1234[/TD]

</tbody>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
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
 
Upvote 0
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
Result after running code
Sheet1Sheet2 In BothNot in Sheet1Not in Sheet2
Not in sheet2Not in sheet1
Not in sheet1
Not in sheet1
Not in sheet1

<tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="colspan: 2, align: center"]Comparing[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="align: right"]1345[/TD]
[TD="align: right"]1345[/TD]

[TD="align: right"]1345[/TD]
[TD="align: right"]5897[/TD]
[TD="align: right"]4789[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="align: right"]1356[/TD]
[TD="align: right"]1356[/TD]

[TD="align: right"]1356[/TD]
[TD="align: right"]1345[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: right"]4789[/TD]
[TD="align: right"]5897[/TD]

[TD="align: right"]1234[/TD]
[TD="align: right"]4789[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="align: right"]1234[/TD]
[TD="align: right"]1234[/TD]

[TD="align: right"]1345[/TD]
[TD="align: right"]1234[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="align: right"]1345[/TD]
[TD="align: right"]1345[/TD]

[TD="align: right"]1652[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="align: right"]1652[/TD]
[TD="align: right"]1652[/TD]

[TD="align: right"]4789[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]

[TD="align: right"]4789[/TD]
[TD="align: right"]4789[/TD]

[TD="align: right"]1345[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

[TD="align: right"]1345[/TD]
[TD="align: right"]1345[/TD]

[TD="align: right"]1789[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]

[TD="align: right"]1789[/TD]
[TD="align: right"]1789[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]

[TD="align: right"]1345[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]

[TD="align: right"]4789[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]

[TD="align: right"]1234[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
Result after running code
Sheet1Sheet2 In BothNot in Sheet1Not in Sheet2
Not in sheet2Not in sheet1
Not in sheet1
Not in sheet1
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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="colspan: 2, align: center"]Comparing[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="align: right"]1345[/TD]
[TD="align: right"]1345[/TD]

[TD="align: right"]1345[/TD]
[TD="align: right"]5897[/TD]
[TD="align: right"]7879[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="align: right"]1356[/TD]
[TD="align: right"]1356[/TD]

[TD="align: right"]1356[/TD]
[TD="align: right"]1345[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

[TD="align: right"]7879[/TD]
[TD="align: right"]5897[/TD]

[TD="align: right"]1234[/TD]
[TD="align: right"]4789[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]

[TD="align: right"]1234[/TD]
[TD="align: right"]1234[/TD]

[TD="align: right"]1345[/TD]
[TD="align: right"]1234[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]

[TD="align: right"]1345[/TD]
[TD="align: right"]1345[/TD]

[TD="align: right"]1652[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]

[TD="align: right"]1652[/TD]
[TD="align: right"]1652[/TD]

[TD="align: right"]4789[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]

[TD="align: right"]4789[/TD]
[TD="align: right"]4789[/TD]

[TD="align: right"]1345[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]

[TD="align: right"]1345[/TD]
[TD="align: right"]1345[/TD]

[TD="align: right"]1789[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]

[TD="align: right"]1789[/TD]
[TD="align: right"]1789[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]12[/TD]

[TD="align: right"]1345[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]13[/TD]

[TD="align: right"]4789[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]14[/TD]

[TD="align: right"]1234[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
Are you trying to compare the differences between the 2 lists, or the differences between the individual rows?
 
Upvote 0
In that case I still don't understand, how can 4789 (which is in both lists) be "Not in Sheet1"?
 
Upvote 0
Sorry, I am comparing each individual number in both list , as 4789 is appearing twice in sheet2 but appearing one time in sheet1
 
Upvote 0
Ok, I understand now, but not sure I can help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Time value to contain Months/Date/Year Hour:Minutes:Seconds AM/PM
Replies
4
Views
715
hsandeep
H
H
  • Question Question
Value corresponding to flag cell
Replies
5
Views
1K
Fluff
Fluff
H
  • Question Question
Code needed for Max & Min Values
2 3 4
Replies
37
Views
4K
hsandeep
H
H
  • Question Question
Multiple IF
2
Replies
13
Views
2K
TAPS_MikeDion
TAPS_MikeDion
H
  • Question Question
Increase OR Decrease the complete value
Replies
3
Views
747
joeu2004
J

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

Share this page

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
Back
Top