Comparing columns in excel

Microsoft Excel 2007
January 22, 2010 at 11:55:08
Specs: Windows XP
Hi all,
I have column A with 500 names and column B with 1500 names.The names in column A are all supposed to be in B.I want to filter out the names that are in A but not in B to another Column C. How do I go 'bout this.


See More: Comparing columns in excel

Report •

January 22, 2010 at 12:31:20
Formula-wise, you could put this Column C and drag it down:


If the value in Ax isn't found in B1:B1500, the value in Ax will be placed in Cx.

Since your Not Found values will be spread out within C1:C500, you would then select Column C, do a Copy...Paste Special...Values to remove the formulae and then sort to bring all the values to the top of Column C.

Macro-wise, you can use this code. No sorting would be required.

Option Explicit
Sub A_Not_In_B()
Dim valueA, nxtRow, valFound
'Prepare for errors when no match is found
On Error GoTo errHandler
'Check values in A1:A500
  For Each valueA In Range("A1:A500")
'Use Match function to look for values from A in B1:B1500
   valFound = Application.WorksheetFunction.Match(valueA, Range("B1:B1500"), 0)
'If Match throws up an Error because value wasn't found, then increment 
'the Row Counter and place the "Not Found" value from A in Column C
  nxtRow = nxtRow + 1
  Range("C" & nxtRow) = valueA
  Resume Next
End Sub

Report •

January 22, 2010 at 13:56:25
Thks alot DerbyDad03, I am very grateful 'cos u've made my day.The formula worked for me.

Any clue on how I can bring all the scartered names in the third(New) column C together?

Report •

January 22, 2010 at 14:10:49
re: Any clue on how I can bring all the scartered names in the third(New) column C together?

Well, you could re-read my response in which I explained exactly how to do that.

Report •

Related Solutions

January 23, 2010 at 05:37:58

Here are some formulas that will always have your list of missing names together without spaces.
Your short list of names is in column A starting at A2
Your long list is in column B starting at B2

In column C, put this formula in cell C2
Drag the formula down as many names as there are in column A.

In column D, put this formula in cell D2=
INDIRECT("A" & TEXT(IF(ISERROR(SMALL($C$2:$C$20,CELL("row",A2)-1)),"5000", SMALL($C$2:$C$20,CELL("row",A2)-1)),"#0"))
Drag the formula down to allow for as many unmatched names as you are likely to get, or down as many rows as there are names in column A.

Change the size of the ranges $B$2:$B$20 and $C$2:$C$20 as required. For this example I only used 20 names 19 names in the short list in column A

In cell A5000 enter =""
This cell is used to blank cells below the list of non-matching names.
You can change the cell used (formula in column D) - but it must be in column A

When this is working you can drag the column C formulas out of the way, say to column AA where they won't be seen, and drag column D formulas into column C

Your list of unmatched names will update dynamically.


Report •

Ask Question