UKC

/ Excel Conditional Formatting.

Please Register as a New User in order to reply to this topic.
ThunderCat - on 14 Feb 2018

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...)

 

trouserburp - on 14 Feb 2018
In reply to ThunderCat:

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

 

 

trouserburp - on 14 Feb 2018
In reply to ThunderCat:

Toying around with it, it's actually testing in alphabetical order for anything 'Greater than' or 'Less than' according to the symbols

 

ThunderCat - on 14 Feb 2018
In reply to trouserburp:

> 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

b

<x

then it highlights the "<x" bit, but not both elements.

Only seems to work for non-numerics though.....

 

Weird.


Cheers all.  I'm going to have to strip out all of the < characters from my data...

 

spenser - on 14 Feb 2018
In reply to ThunderCat:

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?

https://exceljet.net/formula/remove-characters-from-left

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.

 

trouserburp - on 14 Feb 2018
In reply to spenser:

Or do Replace all and swap < for a less offensive bracket

 

spenser - on 14 Feb 2018
In reply to trouserburp:

< 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.

ThunderCat - on 14 Feb 2018
In reply to spenser:

> 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.

 


Please Register as a New User in order to reply to this topic.