I'm trying to highlight duplicates in a excel sheet which we have listing our network ports.
Even though the cells are formatted as text, when in the
=COUNTIF(E:E,E4)>1 forumula they appear to be treated as numbers. That is, floor port
1.10 is a match to
1.100 as well
I realise I could re-format the numbers to be correct,
1.001, 1.010 and 1.100 however this is a lot of work for something that's eventually going to be dropped into a database anyway.
Is there some way of doing the above formula (which works great for highlighting duplicates) but doesn't match them as numeric values? I've found lots of discussion of this problem involving file names, but as they always have
. in them, it seems to centre around that and I'm unsure how to adapt it to my case?
I did try using the built in conditional formatting to highlight duplicates however this appeared to be applied once, and not after any cell changes. If that can be fixed so it's re-checked each cell change, then that will also work.
| 1.1 | 1.10 | 1.100