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

Formula-wise, you could put this Column C and drag it down: =IF(ISERROR(MATCH(A1,$B$1:$B$1500,0)),A1,"")

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

Since your

Not Foundvalues 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) Next '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 errHandler: nxtRow = nxtRow + 1 Range("C" & nxtRow) = valueA Err.Clear Resume Next End Sub

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?

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.

Hi, 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 B2In column C, put this formula in cell C2

=IF(ISNA(VLOOKUP(A2,$B$2:$B$108,FALSE)),VALUE(CELL("row",A2)),"")

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

Regards

Ask Your Question

Weekly Poll