Comparing columns

[nysos3]

Basically I'm trying to compare 2 columns that have upwards of 1000 rows. The best way to explain what it is that I'm trying to do is give an example...
Let's say Column A has a, b, c, d, e, and f. Column B has a, c, d, and f. I want column C to show that b and e are missing. On a much larger scale that I'd rather not have to scroll through a whole bunch of N/As or blanks to find the few bits of real missing data (Only 7 or 8 things, out of 1000+, should be missing in column B from column A.)

Sorry if that doesn't make sense.

 

[TheCman81]

Will Column A have a fulll list with nothing missing?
Also the data in column B, how is that inputed?

 

[nysos3]

Column A will have a full list.
Both data is basically being copy/pasted in from a different spread sheet. The formatting of the data is the same.

 

[TheCman81]

Without actually seeing your data I can only assume something like this

Excel 2010
	A	B	C
1	Head1	Head2	Head3
2	a	a	FALSE
3	b	#N/A	TRUE
4	c	c	FALSE
5	d	d	FALSE
6	e		TRUE
7	f	f	FALSE
Sheet1

Cell Formulas
Range	Formula
C2	=OR(ISBLANK(B2),ISERROR(B2))

If not, can you post an outake of your spreadsheet so I can work with it?

 

[Marcelo Branco]

Maybe something like this

A B C

Dataset1	Dataset2	Missing
a	a	b
b	c	e
c	d	k
d	f
e
f
a
k

<tbody>
</tbody>

Array formula in C2 (Excel 2007 or higher)
=IFERROR(INDEX($A:$A,SMALL(IF($A$2:$A$1000<>"",IF(ISNA(MATCH($A$2:$A$1000,$B:$B,0)),ROW($A$2:$A$1000))),ROWS($1:1))),"")

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy down

M.

 

[nysos3]

Array formula in C2 (Excel 2007 or higher)
=IFERROR(INDEX($A:$A,SMALL(IF($A$2:$A$1000<>"",IF(ISNA(MATCH($A$2:$A$1000,$B:$B,0)),ROW($A$2:$A$1000))),ROWS($1:1))),"")

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy down

M.

This was EXACTLY as I needed. Thank you SO much!

 

[Marcelo Branco]

This was EXACTLY as I needed. Thank you SO much!

You are welcome and thanks for the feedback!

M.