Comparing columns

[bigbearbricktop]

Afternoon

I wonder if anyone can help me, what I need to do seems simple but I cant work it out

I have two columns in Excel and I need to check to make sure that all the ref numbers in Column A are in Column C. If not I need them to be highlighted or a different colour. I wouldnt expect them to be in the same row, the ref number could be anywhere in Column C. For instance I wouldnt expect A1 to contain same ref number as C1 but I would expect A1's ref number to appear somewhere in Column C.

How could I do this?

 

[VoG]

Try selecting column A then using Conditional Format with the formula

=ISERROR(MATCH(A1,C:C,0))

and set a suitable fill colour.

 

[Peter_SSs]

My guess is that you will probably want to exclude any blank cells from the formatting, so my suggestion is:

Select A1:A?? and apply the Conditional Formatting shown.

If you need further direction, please advise what version of Excel you are using.

Excel Workbook
	A	B	C
1	a		a
2	b		x
3			y
4	c		f
5	d		b
6	e		q
7	f		a
8			d
9
10
Compare Columns

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =AND(A1<>"",COUNTIF(C:C,A1)=0)	Abc

 

[bigbearbricktop]

Thanks for your help guys VoGs response sorted it, thanks again!