Function Exact in Conditional Formatting

[Nargalen]

Hello,

I'm trying to use EXACT function in Conditional Formatting when highlighting cells with different content. I use this function instead of simple "Format cells that contain unique values" because this option doesn't work for case sensitive values (which I have to sort out). My goal is to compare two columns (E and H in the following picture) and highlight the cells that don't have a counterpart in the other column. As proved by the picture below, a string "bytes per packet" (column E) is not green, although it has no counterpart in the column H. The formula in the dialog is already applied. Does anybody have any idea?

Thank you in advance for any help!

Nargalen.

 

[Nargalen]

I've just noticed I've posted it with EXACT and not NOT(EXACT()) picture. Sorry for that, but this shows too that EXACT doesn't work as it should, because all the strings like "Bytes per package" that do have a counterpart are still grey, unhighlighted.

 

[FormR]

Hi, in the formula in CF do not use full column references - instead use the row number for the first cell where the condition is applied.

 

[FormR]

Hi, I re-read your post - you can try this formula to conditionally format the cells in E2 onwards where these is no exact match in the range H2:H1000

=NOT(ISNUMBER(MATCH(TRUE,EXACT(E2,$H$2:$H$1000),0)))

 

[Nargalen]

Yes! Thank you, FormR, your second reply solves it perfectly. I don't know why did I expect EXACT to work as I showed above. Thanks again!

 

[Nargalen]

Well, it turns out that I was celebrating too early (but thank you anyway).

This marks as unique some cells that are exact copies while it ignores some real uniques. And it does it quite irregularly, I cannot tell where the problem is now. Sometimes, however, it does what it should. Maybe I should mention that I work with three columns totally, comparing each with the other two. Each column has around 3000 rows. Maybe this amount of data has some negative effect on Excel itself, but I don't think so. Any other ideas?

 

[Joe4]

This marks as unique some cells that are exact copies while it ignores some real uniques. And it does it quite irregularly, I cannot tell where the problem is now.

I have seen this happen when people don't have their ranges aligned properly.

Note that when applying CF to a whole range of cells, you need to write the CF formula as it to the very first cell in the selected range.
If you are selecting whole columns, your first row is actually row 1.

But the formula that FormR gave you only works if you start in row 2:

you can try this formula to conditionally format the cells in E2 onwards where these is no exact match in the range H2:H1000

=NOT(ISNUMBER(MATCH(TRUE,EXACT(E2,$H$2:$H$1000),0)))

 

[Nargalen]

Thanks, Joe, it seems that now it works fine.
Thank you both for the answers.