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.1 and 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.
Example Data:
| 1.1
| 1.10
| 1.100
Best Answer
Based on what I understand your question, see if below solution could work for you. You could use combination of SUM, IF & EXACT Excel functions to construct an Array Formula.
See the below example and confirm if this is what you are looking for.
In this example sample data is in Cells C2:C5. Comparing against C2 the formula in result cell E2 is
Warp it as an Array Formula by pressing CTRL + SHIFT + ENTER from within the formula bar. The formula shall now be enclosed in Curly Braces to indicate that it's an Array Formula.
C2:C5 is formatted as Text.