MATCH, FIND & Conditional Formatting

[ExcelJunkie]

Hi:

I would like to use a function that checks an entire range A2:A100 and if it finds any duplicate TEXT items (like two or more different cells that contain "Open" in that range) - I want to show the duplicate item found in cell A1 or it can highlight all the cells that are duplicate with a different color.

Thank you!

p.s., I want to disregard blank cells in the search for duplicate items.

[Dav]

Its not entirely clear what you want. If you want duplicate cells highigted in conditional formating that easy

formula is countif($a$2:$a$10,a2) and format to a colour fo your choice

However what is not clear from your post is is the text the only entry in the cell if one cell contains "open door" and another "open and shut" they would not be considered duplicate.

Also if more than one item is duplicate eg the list contain open, open, shut, shut ,closed

the items that are open and shut will be highlighted, as they are duplicates but not duplicates with each other

Regards

Dav

[daddylonglegs]

I think you'd need to use

=countif($a$2:$a$10,a2)>1