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

Look at the Related Posts box. Your question is fairly common and has been answered numerous times in the forum. e.g.

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?

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 SubIf 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...Values

Then - Sortor

Array method=IF(AND(A1<>$B$1:$B$20),A1,"")

Enter this in C1, using Ctrl-Shift-Enter

Drag down to C30You should see the missing values in the same row that they appears in Column A.

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

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

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.

AWESOME! Macro works great!! Made my Friday.

Glad to have been of assistance.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History