# Excel Formula to compare two rows



## saindra (Mar 22, 2009)

Hi all, 

I have a excel sheet which has two columns A and B. Both the column contains names and column B has more names than in column A. I am looking for a excel formula that can compare names in A and B and list out names in column C that are in column B and is missing in column A

Help will me much appreciated.


----------



## xstext (Mar 19, 2009)

That task is too complex for just a formula
(unless you want multiple columns of various formulas that segment the task into parts, which will ultimately still not provide a very elegant solution).

This would best be handled using vba
(imo)

(although you might get some takers on a multiple formula solution. But that job would be too convoluted and primitive for my tastes)

Is a vba solution an option for you ?


----------



## Fluffmatic (Mar 21, 2009)

I would usually load the file into an MS Access database when I have to do this, a very simple "find unmatched" query wizard will do the trick for you.

If you don't have MS Access then you could potentially use the VLOOKUP worksheet function to do this. I would probably do this as follows:

1) Split the columns into two sheets, one on each sheet and add another column to the right on each sheet which contains the value "1"
2) In cell A3 enter the formula =VLOOKUP(A1,sheet2!$A1:B200,2,FALSE)

This should return a "1" in cell A3 if the value in A1 is contained in sheet 2, and either N/A or error if its not (what was originally your column B).

You can then filter column C to show just the "1"'s, which should be the information you're looking for.

Step two assumes 200 rows, you'll probably want to set that formula to correctly specify your data range, and you may have to change FALSE to TRUE, I can never remember which way round that one goes...

Repeating this by adding a column of Vlookups to sheet 2 will show you the matches that way too.

Hope this helps, feel free to continue the thread if I've not be clear or it doesn't work


----------



## bomb #21 (Jul 1, 2005)

" ... list out names in column C that are in column B and is missing in column A"

Use this in column C as far down as you have values in column B:

*=IF(ISNA(MATCH(B1,A:A,0)),B1,1)*

to return either the name from B or the value 1.

Then select all of column C, press F5, click Special. Select Formulas, *UN*check Numbers, click OK. Hit CTRL+C to copy the names, select D1, then Edit > Paste Special > Values > OK.

Delete column C to top & tail. HTH

ETA: welcome to the board.


----------



## Fluffmatic (Mar 21, 2009)

Hi bomb,

I prefer your way to mine!

I've never come across the match function, I must remember that one, cheers


----------



## bomb #21 (Jul 1, 2005)

Hi back.


----------

