Two columns one check

[Brown]

Good morning,
Thank you for your help, in advance.

I have a need and I will do my best to describe it.

I have two columns C and M with data i am getting from a Macro I run.

How can I find out if ID Numbers in column C match an ID Number in M?
Is there was way, conditional formatting comes to mind but i know that wont work for separate columns.

Thanks for the information.

If i am being unclear I will do a better job of explaining.

Brown

 

[TheRedCardinal]

What do you want to happen if it finds a match?

 

[Joe4]

To see if the value in cell C2 appear anywhere in column M, this formula would do:
=IF(COUNTIF(M:M,C2)>0,"MATCH","NO MATCH")

If you wanted Conditional Formatting, you could use something like:
=COUNTIF(M:M,C2)>0

 

[jmacleary]

You could use conditional formatting on column C. Try something like:
=NOT(ISNA(VLOOKUP(C1,M:M,1,FALSE))) as conditional format in column C.

 

[Brown]

OK, thanks guys.
let me tell you a little more of what i am doing.

I am taking attendance and checking it against a master list of all enrolled members

the macro i run gives allows me to find all absent people (using ID numbers) Column C. I have a master list (of all participants) Column M.

I want to have the ID numbers in C and M that match highlight in M.

Does that make sense?

So if column C and column M have matching numbers column M will be highlighted.

Thanks
Brown

 

[Joe4]

Basically, you are just reversing the process and formula from my first post.

Highlight column M, and enter the following Conditional Formatting formula:
=COUNTIF(C:C,M1)>0
and then select your desired highlighting color.

 

[Brown]

Thank you so much, I m trying it now.
Brown

 

[Brown]

Perfect! Thanks so much.
Keith

 

[Joe4]

You are welcome.

I hope it makes sense. Basically, you are just counting how many many times the entry in cell M1 appears in column C.
If you want to see if it appears at all, you use >0
If you want to see if it does not appear at all, you would use =0

When you select the whole column like that, and write the formula like that, Excel will automatically adjust the formula for each row (so the next will look at M2, etc).

 

[Brown]

I have one more question. How do, or can I, make this formula in conditional formatting work in the entire work book? I will have about 10 tabs.
If i have to load them in each page i will but i wanted to check with you guys for a shortcut. I could not figure that out this morning.
Thanks
Brown

It makes perfect sense.