Comparing Data - Ms Excel

[ramadevidokkuud]

Hello,I've got two separate worksheets as given bellow, I need to be able to compare the two columns and get result in sheet3 as given bellow.


Sheet 1:
Colum 1
AA
BB
CC
DD
EE
FF
GG
HH
II
JJ


Sheet 2:
Colum 1
AA
CC
FF
GG
II


But i want

Sheet 3:
BB
DD
EE
HH
JJ

Can this be done?

 

[hiker95]

ramadevidokkuud,

Welcome to the MrExcel forum.


Sample worksheets (Sheet1, Sheet2, and Sheet3) before the macro:

Excel Workbook
	A
1	AA
2	BB
3	CC
4	DD
5	EE
6	FF
7	GG
8	HH
9	II
10	JJ
11
Sheet1

Excel Workbook
	A
1	AA
2	CC
3	FF
4	GG
5	II
6
Sheet2

Excel Workbook
	A
1
2
3
4
5
6
Sheet3

After the macro:

Excel Workbook
	A
1	BB
2	DD
3	EE
4	HH
5	JJ
6
Sheet3

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub CompareData()
' hiker95, 05/18/2011
' http://www.mrexcel.com/forum/showthread.php?t=551092
Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet
Dim c As Range, FR As Long, NR As Long, a As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
Set w3 = Worksheets("Sheet3")
w3.UsedRange.Clear
NR = 0
For Each c In w1.Range("A1", w1.Range("A" & Rows.Count).End(xlUp))
  FR = 0
  On Error Resume Next
  FR = Application.Match(c, w2.Columns(1), 0)
  On Error GoTo 0
  If FR = 0 Then
    NR = NR + 1
    w3.Cells(NR, 1) = c
  End If
Next c
For Each c In w2.Range("A1", w2.Range("A" & Rows.Count).End(xlUp))
  FR = 0
  On Error Resume Next
  FR = Application.Match(c, w1.Columns(1), 0)
  On Error GoTo 0
  If FR = 0 Then
    NR = NR + 1
    w3.Cells(NR, 1) = c
  End If
Next c
w3.Range("B1").Formula = "=COUNTIF($A$1:A1,A1)"
w3.Range("B1").AutoFill Destination:=w3.Range("B1:B" & NR)
With w3.Range("B1:B" & NR)
  .Value = .Value
End With
For a = NR To 1 Step -1
  If w3.Cells(NR, 2) > 1 Then w3.Rows(a).Delete
Next a
w3.Range("B1:B" & NR).ClearContents
w3.Activate
Application.ScreenUpdating = True
End Sub

Then run the CompareData macro.