Spotting differences between 2 columns

**teddy379** · 10-24-2012, 05:33 PM

I have 2 columns containing names. Column A and B. I'd like to fill in columns C and D with the differences between A and B, So, whatever is missing from A would be placed in C and whatever is missing from B would be placed in D.
I've looked up many threads but nothing worked.
Also, I'm using the Skydrive Office, so something complex probably won't work.

I've had a similar sheet on Google Docs and those were the 2 formulas that worked for me:
=FILTER(A2:A;ISERROR(MATCH(A2:A;B2:B;FALSE)))
and
=FILTER(B:B2;ISERROR(MATCH(B:B2;A:A2;FALSE)))

**teddy379** · 10-24-2012, 08:15 PM

bump...........

**Richard Buttrey** · 10-24-2012, 08:21 PM

Hi,

In C1
=IF(ISERROR(MATCH(B1,A:A,FALSE)),B1,"")
In D1
=IF(ISERROR(MATCH(A1,B:B,FALSE)),A1,"")

and both copied down.

Please also update your location. It is often useful when giving replies since it helps determine what your system locale settings may be.

**teddy379** · 10-25-2012, 03:55 AM

It fills the cell with 41095; which has no relevance to the 2 lists.
Also, what does "both copied down" mean? Do I have to type it in each cell in the C column? Is it supposed to fill the cells with False or True? I've tried several formulas that I've looked up and they either gave me the 41095 thing or False for some reason.

**teddy379** · 10-25-2012, 07:43 PM

bump............

**grizzly6969** · 10-25-2012, 08:52 PM

Richards formulas work fine for me --- perhaps you could post a sample workbook

**FDibbins** · 10-25-2012, 08:58 PM

the number that it gave you looks like a date formatted at as a number. what data types do you have in A and B? probably dates. try and format C to dates and see if they make sense?

what Richard meant by "both copied down" was for you to do just that...copy both formulas down the entire length of the range of data in A and B. you dont vave to copy them 1-by-1 tho, either drag the little handle (square)at the bottom right of the cell, all the way down, or copy, highlight range and paste