Checking a column against a list

[drgogo]

I have had a look around forums for a solution to this problem, but to no avail.

This is what I want to do.

I have on one page a column of registration numbers, which can in some cases appear more than once.

On another page i have a list of registration numbers that i need to check

What i want is a function to check the column on page one and compare these to the list on the other page and then highlight the reg numbers which dont match.

G

[teylyn]

You can do the highlighting with conditional formatting. Or you could set a flag in the adjacent column.

To set a flag in the next column, use a formula like this and copy down

=if(isna(match(A1,Sheet2!$A$1:$A$100,0)),"no match","")

edit: corrected formula typo

Or to change the color for values with no match, select the whole range, then click Format - Conditional formatting - formula is -

=isna(match(A1,Sheet2!$A$1:$A$100,0))

edit:corrected formula typo. sorry for the confusion

then set a format

in both suggestions, adjust ranges and sheet names to reflect your data source.

hth

[drgogo]

Thanks for the advise

I tried the code below

Please Login or Register  to view this content.

but keep getting an error on "A1" part of the formula

[DonkeyOte]

not reviewed the thread in full but re: last post - it's a typo - change the comma post MATCH to open parenthesis

Please Login or Register  to view this content.

[teylyn]

Thanks DO for picking that up. I corrected the typos in my original post.

[drgogo]

Thanks for that correction, but entered the formula into the conditional formatting user box and am informed that you can not make a ref to another sheet within conditional formatting....

[DonkeyOte]

You will need to "Name" the range Sheet2!A1:A100 and use the Name in the Conditional Formatting formula.

for "how to" see: http://www.contextures.com/xlNames01.html