Compare & Elimanate

[George]

I have 2 spreadsheets. Sheet "X" contains approx 250 rows
with the rows in column A containing a unique reference,
other 8 columns various data. Spreadsheet "Y "contains
say 2000 rows with the rows, column A containing a unique
reference. Etc.
In spreadsheet "Y" among column A references are the same
unique references contained in my spreadsheet "X". I am
looking for a method to compare the two sheets and to
eliminate the records in sheet "Y" that do not correspond
to the references in spreadsheet "X" column A.

Many thanks,
Geo

 

[Jim May]

In your sheet "Y" add a helper column (at end) and enter:
=IF(ISNA(VLOOKUP(A1,X!$A$1:$A$4,1,0)),"Not Found","OK") << where A1 is your
reference in "Y" and $A$1:$A$4 is your unique references in sheet "X"
Copy down.
Do a autofilter on the helper column for "Not Found" and delete those rows.
Unfilter and delete the helper column.

Try this on A COPY of your file, not the original... !!!

 

[Guest]

George,

Although it's not exactly what you're looking for, here's some code that
allows you to compare two lists and find out which items only appear in list
1, which only appear in list 2 and which appear in both.

You could change the references to reflect your two worksheets' locations
and add code to delete the row of the cell currently being processed.

Hope this is useful.

Option Explicit

Sub ListCompare()
Dim CompSheet As Worksheet
Dim List1, List2 As Object
Dim List1Item, List2Item As Object
Dim List1Header, List1OnlyHeader, List2Header, List2OnlyHeader,
ListBothHeader As Object
Dim Flag As Boolean

'In my example, List1 is E3:E32 and List2 is J3:J32, although the code
works out
'how long the lists are and allocates the names List1 and List2 to the
cells containing them.

'Make sure that there is a blank column to the left of List1Header, and
blank
'columns between List1OnlyHeader and List2OnlyHeader, and between
List2OnlyHeader 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, List1OnlyHeader 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 List1Header = CompSheet.Range("List1Header")
Set List1OnlyHeader = CompSheet.Range("List1OnlyHeader")
Set List2Header = CompSheet.Range("List2Header")
Set List2OnlyHeader = CompSheet.Range("List2OnlyHeader")
Set ListBothHeader = CompSheet.Range("ListBothHeader")

If List1Header.CurrentRegion.Rows.Count = 1 Then
MsgBox ("You don't have any entries in List 1!")
Exit Sub
End If

If List2Header.CurrentRegion.Rows.Count = 1 Then
MsgBox ("You don't have any entries in List 2!")
Exit Sub
End If

List1Header.Offset(1, 0).Resize(List1Header.CurrentRegion.Rows.Count -
1, 1).Name = "List1"
List2Header.Offset(1, 0).Resize(List2Header.CurrentRegion.Rows.Count -
1, 1).Name = "List2"

Set List1 = CompSheet.Range("List1")
Set List2 = CompSheet.Range("List2")

'Clear List1 only entries produced when macro last run
If List1OnlyHeader.CurrentRegion.Rows.Count > 1 Then
List1OnlyHeader.Offset(1,
0).Resize(List1OnlyHeader.CurrentRegion.Rows.Count - 1).ClearContents
End If
'Clear List2 only entries produced when macro last run
If List2OnlyHeader.CurrentRegion.Rows.Count > 1 Then
List2OnlyHeader.Offset(1,
0).Resize(List2OnlyHeader.CurrentRegion.Rows.Count - 1).ClearContents
End If
'Clear ListBoth entries produced when macro last run
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!")
List1OnlyHeader.Offset(List1OnlyHeader.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!")
List2OnlyHeader.Offset(List2OnlyHeader.CurrentRegion.Rows.Count,
0).Value = List2Item.Value
Else 'Included only for completeness - you already worked out which
items
'were in both lists in the previous loop!
'MsgBox (List2Item.Value & " is in both Lists!")
'ListBothHeader.Offset(ListBothHeader.CurrentRegion.Rows.Count,
0).Value = List2Item.Value
End If
Next

'Sort List1Only list
List1OnlyHeader.CurrentRegion.Sort Key1:=Range("List1OnlyHeader"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

'Sort List2Only list
List2OnlyHeader.CurrentRegion.Sort Key1:=Range("List2OnlyHeader"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

'Sort ListBoth list
ListBothHeader.CurrentRegion.Sort Key1:=Range("ListBothHeader"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

End Sub



Pete