Find Match in Excel 2003

[ajb1131]

I am wondering how to find matches between 2 columns no matter what the order? I would like to post the result in a third column.

I have not put much effort into figuring this out on my own. I am working on a demanding project and honestly don't have the time. I could do this task manually but i have about 10,000 cells to find and compare.

Any help is appreciated. Thanks in advance.

C1 C5 C1 Match Found
C2 C1 C2 Match Found
C3 C6 C3 Match Found
C4 C2 C4 Match Found
C5 C3 C5 Match Found
C6 C4 C6 Match Found
C7 NA Match Not Found

[rylo]

Hi

Assuming that your data is in the range A1:B7 then

C1: =IF(COUNTIF(B:B,A1),A1 & " Match Found","Match Not Found")
Copy down as required.


rylo

[ajb1131]

That worked great. Thank you. I have one more addition that I need help with. I would like to color the B column red where the match is found.

I appreciate the help.

[GOOS]

If you apply conditional formatting to the cells with the function, just say something similar to:

If Cell Value = "Match Not Found" then --> SET YOUR CELL FORMAT

You will probably have to set this up for one cell, and make sure it works. Then, go back into the conditional formatting and remove the absolute reference ('$' symbols) from the cell reference. Make sure it still works. If it does, you can copy the cell, then Paste --> Formats to the other cells. ~R

[rylo]

Hi

B1: Format, Conditional Formatting, Formula Is: =COUNTIF($A:$A,B1)>0, set your required format, OK

Copy B1 and paste special formats as required.


rylo

[ajb1131]

Thanks for all the help. I am still having trouble with getting the cell to change color. If anybody can help me out i do appreciate it.



Please see attachment.

[shg]

Select cells A8:A5147, do Format > Conditional Formatting, Formula is: =COUNTIF(A8, $B$8:$B$678), set whatever formatting you want, press OK.