Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how do i find out what is uncommon in column list A1: A10 and B1:.
i want to compare data in list 1 in column A with list 2 in column B and make
a column C which shows data which is not in A A B C apple apple cat cat ball rubber ball dog rubber dog |
#2
|
|||
|
|||
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.CurrentRegio n.Rows.Count, 0).Value = List2Item.Value End If Next End Sub Hope this helps Pete "vikasarora07" wrote: i want to compare data in list 1 in column A with list 2 in column B and make a column C which shows data which is not in A A B C apple apple cat cat ball rubber ball dog rubber dog |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to find the Average from Column A - but Reference Column B | Excel Worksheet Functions | |||
need to find which numbers (3+) in a column sum to a value | Excel Discussion (Misc queries) | |||
How do I find duplicates in a list | Excel Discussion (Misc queries) | |||
How do I find out what items are in one list but not in another l. | Excel Discussion (Misc queries) | |||
Insert column entries from a master list | Excel Worksheet Functions |