/ Excel Conditional Formatting.
Weirdness in Excel 2007, with the "Conditional Formatting -> Highlight Duplicate Values" function.
Is the angle bracket a special character...it seems to be throwing the results (or have I got a buggy copy)?
Highlight a large range of cells and select "Home -> Conditional Formatting --> Highlight Cells Rules -> Duplicate Values" (then pick a colour or leave as default pink / red)
Now any duplicate values you enter into the range will automatically highlight (as expected).
However if I enter a value preceded by an angle bracket, it highlights without there being a matching value anywhere else.
What's going on there?
(I have a load of values, all surrounded by angle brackets...they're all being flagged as duplicates when very few of them actually are...)
Assuming you mean <> they are the 'greater than' or 'less than' symbols. It might be looking for anything 'less than' your text. Not sure but might be treating the text as name of a variable.
Trying it on Excel 2013 I see you can't even force it by formatting as text or inserting ' at the start. Space in front does fix it
Anyway yes the <> likely mess it up
Toying around with it, it's actually testing in alphabetical order for anything 'Greater than' or 'Less than' according to the symbols
> Toying around with it, it's actually testing in alphabetical order for anything 'Greater than' or 'Less than' according to the symbols
Yeah, seems to be the case doesn't it..although it's not really highlighting the dupes...
If I enter
then it highlights the "<x" bit, but not both elements.
Only seems to work for non-numerics though.....
Cheers all. I'm going to have to strip out all of the < characters from my data...
Assuming that the < symbol is the first character in the cell you can just use the following and hide the column with the < symbol in?
If that doesn't work or you need to keep the symbolbs in place feel free to drop me an email and I will put together a little array of data to demonstrate another formula which will allow you to do this.
Or do Replace all and swap < for a less offensive bracket
< may well have a use within the spreadsheet.
It is possible to construct a simpler version of the duplicate conditional formatting using the countif function in an adjacent column and the greater than conditional formatting function, this should avoid the issue which the OP is having.
> Assuming that the < symbol is the first character in the cell you can just use the following and hide the column with the < symbol in?
> If that doesn't work or you need to keep the symbolbs in place feel free to drop me an email and I will put together a little array of data to demonstrate another formula which will allow you to do this.
Really kind of you, cheers. Did a "replace all" and got rid of them, then put them back in afterwards with a bit of concatenation, so all happy now
Just threw me because they all of the entries came back pink when I expected very few. Thought the data was massively messed up (know that feeling when you think you've REALLY screwed up?)....then got curious as to what was actually happening.
The smart and attractive solution for all sport climbers and indoor climbing enthusiasts: the full zipper allows complete opening... Read more
According to American climber Andrew Hadesh, there is no place like Yangshuo on Earth. He should know, he wrote the guidebook.... Read more
Overlooking the village of Tremadog, the sun-blessed cliffs of Craig Pant Ifan and Bwlch y Moch offer over 300 routes ranging... Read more
Authorities in the Haute-Savoie region of France have issued a temporary access restriction on the popular /Normal Route up Mont... Read more