From: Marsha M Marsha on
I have used this formula in the past and have been able to then sort on
column C and it would but the number (matches) at the top followed by the
#N/As. Is there a way to eliminate the "sort on" criteria or do this in
another way?

thank you,
Marsha M

"ScottO" wrote:

> One way ...
> If the 194 cells are in ColA and the 150 cells are in ColB, then at
> C1 put the formula
> =MATCH(A1,$B$1:$B$150,0)
> and copy down to C194.
> The cells in ColC that return #NA indicate the missing entries from
> ColB.
> Rgds,
> ScottO
> "Denny" <Denny(a)> wrote in message
> news:10E2A3B5-C5D5-4969-A550-346EBE5457DA(a)
> | I have two different columns which should contain the same contents
> but there
> | are diffences. Each column should have 194 entries (cells) but one
> has only
> | 150. Is there a function in excel which would allow me to compare
> the
> | contents in each and highlight the 44 discrepancies?
From: Paul on

You could just use Conditional Formatting for this, rather than a whole
column of formulas.

Excel 2007 instructions:
Select A1:A194 and click Conditional Formatting -> New Rule...
Select 'Use a formula....'
Type the following formula: =ISERROR(MATCH(A1,$B$1:$B$150,0))
Select a format (fill/font color, etc.)
Click OK a few times to return to the sheet.

Any values in column A that don't appear in column B will now be

Paul's Profile: 1697
View this thread: