# Comparing Columns in Excel 07

March 27, 2009 at 05:51:24
Specs: Windows XP
 Should be a pretty simple question for an Excel guru. I have two columns of text that are nearly identical except a few entries. I need a way to pull entries that appear in Column A but not in Column B and put the results in Column C.Seems like this would be a simple IF statement.....IF Value in A is NOT in B Then Copy to C and IF Value A is in B ignore. Conditional Formatting works to highlight the unique values, but I'd like to separate them the rest of the data.Thanks!!!!

See More: Comparing Columns in Excel 07

#1
March 27, 2009 at 06:54:25
 Look at the Related Posts box. Your question is fairly common and has been answered numerous times in the forum.e.g.http://www.computing.net/answers/of...

Report •

#2
March 27, 2009 at 07:42:29
 Thank you! Brand new to the forum and did not see the related posts area.The post you linked is perfect, but the response is just a bit above my level. The formula looks easy enough but I am unfamiliar with array formulas and how to implement them. Also, I ~think~ the formula is stumbling a bit because my two columns are of diffent lengths. I need to do the following. This is a simplified example.Compare A1:A30 w/ B1:B20 show any value in A that is not in B begining at C1. Questions;1. How exactly do I execute the array? Paste into C1, highlight the ranges with the colored boxes and then type Ctrl+Shft+Enter?2. Will arrays have an issue with cells B21:30 in this case because they are blank?

Report •

#3
March 27, 2009 at 08:27:48
 If you want the values to start in C1 "automatically" i.e. without multiple steps, you'll need to use the VBA code, modified to match your ranges:```Sub CompareCols() For Each MyVal In Range("A1:A30") With Range("B1:B20") Set c = .Find(MyVal, LookIn:=xlValues, LookAt:=xlWhole) If c Is Nothing Then myRow = myRow + 1 Range("C" & myRow) = MyVal End If End With Next End Sub```If you don't mind the extra steps of Copy...PasteSpecial Values and Sorting, then either of these formulae will work:Non-Array Method=IF(ISERROR(MATCH(A1,\$B\$1:\$B\$20,0)),A1,"")Enter this in C1 and drag down to C30. You should see the missing values in the same row that they appears in Column A.Then - Edit...Copy...PasteSpecial...ValuesThen - SortorArray method=IF(AND(A1<>\$B\$1:\$B\$20),A1,"")Enter this in C1, using Ctrl-Shift-EnterDrag down to C30You should see the missing values in the same row that they appears in Column A.Then - Edit...Copy...PasteSpecial...ValuesThen - SortNote that once you have the macro installed, you can use it whenever you make changes to the columns. If you need to compare other columns, some simple modifications will make it work.If you use either of the formuale, then you'll need to reenter the formula and do the extra steps every time.I know which method I would prefer.

Report •

Related Solutions

#4
March 27, 2009 at 08:33:09
 P.S. If the columns contain both numbers and text, then the Sort isn't going to work very well.It will sort the numbers, then the blank cells, then the text. You may need to use the macro method if that's the case.

Report •

#5
March 27, 2009 at 08:57:01
 AWESOME! Macro works great!! Made my Friday.

Report •

#6
March 27, 2009 at 09:21:16
 Glad to have been of assistance.

Report •