In the attached workbook, Excel says the cells "5 - 9" and "5-9" are identical while they are obviously not. It does the same thing for "2-4" and "2 - 4", but not for "15-19" and "15 - 19".
The problem only occurs in column B, not in column A, while I don't see the difference between the two columns.
What am I missing here?
Thanks!
I don't see any indication of the problem that you describe in your attachment.
What formula or operation are you using when you believe that "5 - 9" (B5) is identical to "5-9" (B8), for example?
=B5=B8 returns FALSE
Data > Filter shows only B5, B10, B15, B16 and B18 when I select "5 - 9", and only B8 when I select "5-9".
Please provide an Excel file that demonstrates the problem, with the problem shown, if possible, or at least with step-by-step instructions that allow us to duplicate the problem.
The highlight duplicates functionality seems to work similarly to functions like COUNTIF in that it will treat anything that could be considered a number (including dates) as a number. In this case I assume it is coercing 5-9 to a date (either 5th Sept or 9th May depending on your region). That's why it doesn't happen with 15-19.
Rory
I selected cells B5 (5-9) and B8 (5 - 9) and then colored duplicate values. As you can see in the file the cells are colored. When I use the countif formula, it says "5 - 9" occurs 6 times while it only occurs 5 times.
How can I fix this? I already put a ' in front of the values so that Excel wouldn't change them to a date. Is there another way to make sure these values are not seen as dates?
Not with the highlight duplicates function, as far as I know. You could use a helper column (or CF) formula with SUMPRODUCT, like this:
=SUMPRODUCT(--($B$2:$B$27=B2))
[.... deleted ....]
I think rorya has hit upon the problem, to wit: COUNTIF is interpreting "5-9" and "5 - 9" as dates, thereby comparing everything as numeric dates.
Last edited by joeu2004; 12-18-2019 at 11:27 AM.
FYI, changing COUNTIF($B$2:$B$27,B5) to COUNTIF($B$2:$B$27,"*" & B5) forces COUNTIF to do a string comparison. It produces the intended results.
Thank you both so much!
But on second thought, that is a __bad__ work-around because it allows for counting "xxx5 - 9" as well. Maybe not a problem in the immediate situation; but problematic, in general.Originally Posted by joeu2004
I would use the SUMPRODUCT work-around, because it is predictable.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks