Home |
Search |
Today's Posts |
#1
|
|||
|
|||
comparing columns
Hi guys! I am hoping someone has an answer for this, as I am stuck. I have a spreadsheet with two columns. Each column contains a bunch of codes. I need to be able to identify any code that appears in one column, but not the other (doesn't matter which one). So, if the code appears in column A but not B, I need to know about it. If the code appears in column B but not A, I need to know about it. I know this can be done with a formula, but I am not sure what it is! PLease help! Thanks. -- dfeld71 ------------------------------------------------------------------------ dfeld71's Profile: http://www.excelforum.com/member.php...o&userid=21452 View this thread: http://www.excelforum.com/showthread...hreadid=386934 |
#2
|
|||
|
|||
Put your data is in column A and C.
Then put this in B1 and drag down: =if(isnumber(match(a1,C:C,0)),"Found in column C","Missing from column C") And drag down to match the number of rows in A. And in D1, put this formula: =IF(ISNUMBER(MATCH(c1,a:a,0)),"Found in column A","Missing from column A") And drag down to match the number of rows in column C. You could even add headers and then select A:D and do Data|Filter|Autofilter. And filter to see the ones you want. ======== You may want to read Chip Pearson's treatise on Duplicates: http://www.cpearson.com/excel/duplicat.htm He has lots of hints/techniques you might be able to use. dfeld71 wrote: Hi guys! I am hoping someone has an answer for this, as I am stuck. I have a spreadsheet with two columns. Each column contains a bunch of codes. I need to be able to identify any code that appears in one column, but not the other (doesn't matter which one). So, if the code appears in column A but not B, I need to know about it. If the code appears in column B but not A, I need to know about it. I know this can be done with a formula, but I am not sure what it is! PLease help! Thanks. -- dfeld71 ------------------------------------------------------------------------ dfeld71's Profile: http://www.excelforum.com/member.php...o&userid=21452 View this thread: http://www.excelforum.com/showthread...hreadid=386934 -- Dave Peterson |
#3
|
|||
|
|||
Hi,
Doing it with VBA, you could adpat my code as necessary: Option Explicit Sub ListCompare() Dim CompSheet As Worksheet Dim List1 As Range 'range of cells containing your first list eg B3:B32 Dim List1Header As Range 'label at the top of list 1 e.g. B2 Dim List1Item As Range Dim List2 As Range 'range of cells containing your second list e.g.D3:D32 Dim List2Header As Range 'label at the top of list 1 e.g. D2 Dim List2Item As Range Dim List1OnlyHeader As Range 'label above where you want items in first list only to appear e.g. F2 Dim List2OnlyHeader As Range 'label above where you want items in second list only to appear e.g. H2 Dim ListBothHeader As Range 'label above where you want items in BOTH listsonly to appear e.g. J2 Dim Flag As Boolean 'In my example, List1 is B3:B32 and List2 is D3:D32, 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 F2, List2Header is a label in H2 and 'ListBothHeader is a label in J2. Columns A, D, E, G, I and K must NOT contain any entries. 'The worksheet is called "Compare Lists" 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 Hope this helps! Pete "dfeld71" wrote: Hi guys! I am hoping someone has an answer for this, as I am stuck. I have a spreadsheet with two columns. Each column contains a bunch of codes. I need to be able to identify any code that appears in one column, but not the other (doesn't matter which one). So, if the code appears in column A but not B, I need to know about it. If the code appears in column B but not A, I need to know about it. I know this can be done with a formula, but I am not sure what it is! PLease help! Thanks. -- dfeld71 ------------------------------------------------------------------------ dfeld71's Profile: http://www.excelforum.com/member.php...o&userid=21452 View this thread: http://www.excelforum.com/showthread...hreadid=386934 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing Two Columns of Text | Excel Discussion (Misc queries) | |||
Comparing text in columns | Excel Discussion (Misc queries) | |||
Comparing Data in 2 columns | Excel Worksheet Functions | |||
Comparing data in two columns and highlighting the data | Excel Worksheet Functions | |||
Comparing Data in two columns | Excel Worksheet Functions |