Hi, Vik,
Here's some code I put together that scans two lists and shows you all the
items that are list in list 1, all the items thar are just in list 2 and the
items that appear in both.
See if it's of any use to you
Sub ListCompare()
Dim CompSheet As Worksheet
Dim List1, List2 As Object
Dim List1Item, List2Item As Object
Dim List1Header, List2Header, ListBothHeader As Object
Dim Flag As Boolean
'In my example, List1 is E3:E32 and List2 is I3:I32
'I make sure that there is a blank column to the left of List1Header,
and blank
'columns between List1Header and List2Header, and between List2Header
and ListBothHeader.
'Finally, make sure there is a blank column to the right of
ListBothHeader.
'This ensures that all the "CurrentRegion" referenece work correctly.
'In my example, List1Header is a label in L2, List2Header is a label in
N2 and
'ListBothHeader is a label in P2. Columns K, M, O and Q must NOT contain
any entries.
Set CompSheet = Worksheets("Compare Lists")
Set List1 = CompSheet.Range("List1")
Set List2 = CompSheet.Range("List2")
Set List1Header = CompSheet.Range("List1Header")
Set List2Header = CompSheet.Range("List2Header")
Set ListBothHeader = CompSheet.Range("ListBothHeader")
'Clear List1 only entries from last run of macro
If List1Header.CurrentRegion.Rows.Count > 1 Then
List1Header.Offset(1, 0).Resize(List1Header.CurrentRegion.Rows.Count
- 1).ClearContents
End If
'Clear List2 only entries from last run of macro
If List2Header.CurrentRegion.Rows.Count > 1 Then
List2Header.Offset(1, 0).Resize(List2Header.CurrentRegion.Rows.Count
- 1).ClearContents
End If
'Clear ListBoth entries from last run of macro
If ListBothHeader.CurrentRegion.Rows.Count > 1 Then
ListBothHeader.Offset(1,
0).Resize(ListBothHeader.CurrentRegion.Rows.Count - 1).ClearContents
End If
'Check which items are only in list 1 and not in List 2
For Each List1Item In List1
Flag = False
For Each List2Item In List2
If List2Item.Value = List1Item.Value Then
Flag = True
End If
Next
If Flag = False Then
'MsgBox (List1Item.Value & " is only in List 1!")
List1Header.Offset(List1Header.CurrentRegion.Rows.Count,
0).Value = List1Item.Value
Else
'MsgBox (List1Item.Value & " is in both Lists!")
ListBothHeader.Offset(ListBothHeader.CurrentRegion.Rows.Count,
0).Value = List1Item.Value
End If
Next
'Check which items are only in list 2 and not in List 1
For Each List2Item In List2
Flag = False
For Each List1Item In List1
If List1Item.Value = List2Item.Value Then
Flag = True
End If
Next
If Flag = False Then
'MsgBox (List2Item.Value & " is only in List 2!")
List2Header.Offset(List2Header.CurrentRegion.Rows.Count,
0).Value = List2Item.Value
Else ' included only for completeness - you already did this in the
previous loop!
'MsgBox (List2Item.Value & " is in both Lists!")
'ListBothHeader.Offset(ListBothHeader.CurrentRegion.Rows.Count,
0).Value = List2Item.Value
End If
Next
End Sub
Hope this helps
Pete
