2 Attachment(s)
Excel Conditional formatting with Formula
I'm trying to conditional format a column of data based on whether the value in the cell appears in another column. After a bit of googling I have this working for numbers, but it does not like text.
My actual data is text and numbers - the sheet is just to get the method correct. In the screenshots I would expect "as1234" in column E to be highlighted as "as1234" is present in column A.
Attachment 30917
Attachment 30916
Thanks in advance...
Re: Excel Conditional formatting with Formula
The conditional format will show if the result of your formula is 1 or above, in your text case it returns text which is false for the purpose of conditional formatting
1 Attachment(s)
Re: Excel Conditional formatting with Formula
Not sure if this is exactly what you want but there is a 'duplicate values' rule within the conditional formatting function. Select both ranges you want to compare and then create a new duplicate value rule.
Attachment 30918
Re: Excel Conditional formatting with Formula
http://spreadsheetpro.net/comparing-...unique-values/
Try (apply to col E)
=COUNTIF($A:$A, $E1)
in one column
and apply to col A
=COUNTIF($E:$E, $A1)
to highlight duplicates in both columns as they are entered
1 Attachment(s)
Re: Excel Conditional formatting with Formula
Thanks for the quick responses. I'm not sure the highlighting of duplicates would work as I actually need to do a bit more than I first asked. I want to be able to apply the formula 3 times to get 3 colour formats. See new screenshot - same as original formula, but pointed at the 3 separate columns of data. My actual data will be just cells containing letters and numbers.
Attachment 30919
1 Attachment(s)
Re: Excel Conditional formatting with Formula
Not tested for duplicates in each column but appears to do what I think you're after. Blue column rules. If 65 is in both Amber and Blue, the resulting selection will only be blue. Might have to play around with the ordering of the rules
3 different rules.
Re: Excel Conditional formatting with Formula
Thanks - that does exactly what I wanted. Apologies for the delay in replying, I've been away.