Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 |
#2
![]() |
|||
|
|||
![]()
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... !!! "George" wrote in message ... 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 |
#3
![]() |
|||
|
|||
![]()
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.CurrentRegi on.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.CurrentRegi on.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.CurrentRegio n.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 "Jim May" wrote: 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... !!! "George" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How can I compare the contents of two Excel files? | Excel Discussion (Misc queries) | |||
Compare two spreadsheets to find differences. | Excel Discussion (Misc queries) | |||
Is there a way to compare 2 spreadsheets with Excel? | Excel Discussion (Misc queries) | |||
Compare Question | Excel Discussion (Misc queries) | |||
Is this simple I'm trying to compare values from 4 excel tables | Excel Worksheet Functions |