5

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
4

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

=SUM(IF(EXACT(C2,C3:C5),1,0))

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.

enter image description here

  • Hi,This looks like it could be adapted to what I want - so does the EXACT tell it to compare as text - or at least with the same number of decimal places? – djsmiley2k Jul 5 '18 at 12:21
  • EXACT primarily works on Text formats but it still can compares two values if both are numeric e.g. I can compare numeric 2 with another numeric 2 to be TRUE or even text 2.0 with text 2.0 to be TRUE. Numeric 2 with text 2.0 will result in FALSE. Even cell formatted as Numeric 2.00 with another Numeric 2.000 will result into TRUE as long as both are Numeric – patkim Jul 5 '18 at 12:31
  • I'm trying to use your solution, but I need to apply it to many rows - in your case you've applied to only the first row? When I'm applying it to more rows, it includes the item itself as a dupe....? – djsmiley2k Jul 5 '18 at 13:02
  • You may like to show what formula you are using and how you would like to see the expected results, so that I or other users on this forum can provide a more appropriate solution. You may also like to show what conditional format formula you are using that's not working as expected. Conditional Formatting does work properly even after cell contents change. – patkim Jul 5 '18 at 15:30
  • After some wiggling of the solution I managed to get it working with your help. So thank you. – djsmiley2k Jul 9 '18 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.