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.


See More: Comparing Columns in Excel 07

Report •

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.


Report •

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.

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 •

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
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


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...Values
Then - Sort


Array method


Enter this in C1, using Ctrl-Shift-Enter
Drag down to C30

You should see the missing values in the same row that they appears in Column A.

Then - Edit...Copy...PasteSpecial...Values
Then - Sort

Note 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

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 •

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

Report •

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

Report •

Ask Question