Are there duplicates in each column?
If no, then there are a couple of options that I like to do.
column A of sheet2.
Then use data|Filter|advanced filter to eliminate the duplicates and put the
result in column B
Debra Dalgleish explains how to do this last step:
http://www.contextures.com/xladvfilter01.html#FilterUR
Then I delete column A.
Then I use a couple of formulas in column B and C:
=isnumber(match(a2,sheet1!a:a,0))
(in B2)
=isnumber(match(a2,sheet1!b:b,0))
(in c2)
And drag down as far as required.
Then I apply data|filter|autofilter on those 3 columns.
Filtering to show True in column B and then True in column C shows me the items
in both lists. I can filter to show False/True or True/false and see what items
are missing from the opposite list.
previous posts:
Option Explicit
Sub testme()
Application.ScreenUpdating = False
Dim wks As Worksheet
Dim ColA As Range
Dim ColB As Range
Dim iRow As Long
Dim myCols As Long
Set wks = Worksheets("sheet1")
wks.DisplayPageBreaks = False
With wks
'row 1 has headers!
Set ColA = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set ColB = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
With ColA
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With
'change the mycols to the number of columns that
'are associated with column B
myCols = 1 ' columns B only
With ColB.Resize(, myCols)
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With
iRow = 2
Do
If Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 0 Then
Exit Do
End If
If .Cells(iRow, "A").Value = .Cells(iRow, "B").Value _
Or Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 1 Then
'do nothing
Else
If .Cells(iRow, "A").Value > .Cells(iRow, "B").Value Then
.Cells(iRow, "A").Insert shift:=xlDown
Else
.Cells(iRow, "B").Resize(1, myCols).Insert shift:=xlDown
End If
End If
iRow = iRow + 1
Loop
End With
Application.ScreenUpdating = True
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
========
Remember: Both these techniques are useful if there are no duplicates in each
of the columns.
--
Dave Peterson