/ 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.
Emma Twyford has redpointed 8c at , Spain. The 50m endurance based route was initially graded 8c+, but the general consensus for... Read more
The UKC team recently returned from the annual gear-fest in Munich: ISPO 2018. Here are some video interviews with brand... Read more
Inspired by the popular Humans of New York Facebook series by Brandon Stanton, we thought that sharing short vignettes from a... Read more
In this podcast series, Wil Treasure shares stories from the climbing world through interviews with both well-known and... Read more
A fire caused by an exploding rechargeable head torch battery, has led to warnings for vigilance from Glenmore Lodge, Scotland's... Read more