Finding duplicates in two seperate columns

[KRayKeeling]

This should be an easy one, it has slipped my mind.

I have columns A and B, I want to find any names that appear in both column A and column B. I want them to show in a certain color.

Any ideas?

[NBVC]

Select column A and invoke Conditional Formatting from the Format menu..

Choose Formula Is from 1st drop down and enter formula: =Match(A1,B:B,0)

Click Format and choose highlight colour.

Click Ok, Click Ok to finish.

Select column B and repeat above substituting formula with =Match(B1,A:A,0)

[oldchippy]

Hi,

Take a look here

http://spreadsheetpage.com/index.php...al_formatting/

[KRayKeeling]

What is this formula telling Excel to do exactly?

Is it saying find names in column A that appear in column B, and vice versa?

I have a two different columns. One full of managers, and one full of representatives. I'm trying to make sure that there is not a situation where they are approving for each other

[NBVC]

The first formula is checking if each individual cell in Column A is repeated at all in column B...

The second is checking if each individual cell in column B is repeated at all in column A.

So if there is a duplication in both columns, both those duplicates would be highlighted.

[KRayKeeling]

Oh okay, I see, so my next question is a result of the formula.

It seems that in column B, after some of the names there are spaces. Because of the spaces, Excel isn't recognizing some of the data to be the same.

Is there something I can do about the spaces?

[NBVC]

In an adjacent column enter formula: =Trim(B2) where B2 is first cell after headings....

copy the formula down the column...

Then copy this new column, go to B1 and go to Edit|Paste Special and select Values...

Now you can delete that new column..

You can do the same for Column A if needed.

[KRayKeeling]

Wow! I didn't know you could do that. That's awesome.

The last and final question, promise! Is there a way I can have Excel look to see if any cells are flip flopped?

For example, highlight cells that have in cell A32 "Bob Saget" and in B32 "John Stamos" and then further down the list there is A64 "John Stamos" and in B64 "Bob Saget"?

Is this possible? I think I am getting too picky, but this would be awesome! Thanks for showing me how to trim that data down, amazing...

[NBVC]

Select the entire range from A1:B whatever..... try not to select whole column as the formula may slow things down....

Then invoke CF again... If you already have a conditional format, click Add to add another...

Enter formula: =AND(A1<>"",ISNUMBER(MATCH($A1&$B1,$A$1:$A$10&$B$1:$F$10,0)))

adjust the ranges to suit your data....

Click Format and add your colour scheme...

Note: The first formula in CF will take precedence...

[KRayKeeling]

It seemed to have highlighted everything in the range I selected...

=AND(A1<>"",ISNUMBER(MATCH($A1&$B1,$A$1:$A$65&$B$1:$B$65,0)))

Did I type everything right?

[NBVC]

Sorry.. bad formula...should've tested it more...

try this instead:

=AND(A1<>"",SUMPRODUCT(--($A$1:$A$65&$B$1:$B$65=$B1&$A1)))

[KRayKeeling]

Thank you so much for being so patient with me on this! It worked out great! Thanks again for everything!!