Finding the duplicates and errors.

[kishorkhanal]

I have text and number values in random order in column A. In column E I have same values in A but in different order. I have to manually type both of these values. I want to format cells in column E so that if I type any values other than A, the cell should fill with red and if any value duplicates in either column A or E then the duplicated cells should fill with yellow. Any suggestions.

 

[marka87uk]

For duplicates I would do something like this:

=COUNTIF(A:A,E1)>1

That'll put a TRUE or FALSE in the column depending on whether the value in E is in A or not. Then you can use conditional formatting to format all cells with the value TRUE as yellow, and all as FALSE with red.