Jul 03 2019 01:32 AM
Dear all,
I am trying to identify duplicates based on a unique string composed from multiple cells using the countif function. Now I encounter an issue where the incorrect value is returned for values that are clearly different from each other. In the attached file is the problem I am facing visible.
Can anyone help me with this issue?
Jul 03 2019 01:59 AM - edited Jul 03 2019 02:23 AM
Use this formula in E2, and copy down rows:
=SUMPRODUCT(--(D$2:D2=D2))
By the way, your original formula works if you concatenate CHAR(173) before the criteria argument like this:
=COUNTIF(D$2:D2,
CHAR(173)&D2)
The origin of the foregoing idea is from Lori Miller in this link:
http://dailydoseofexcel.com/archives/2006/10/10/countif-bug/
See both formulas in the attached file.
Jul 03 2019 02:07 AM
Hey @Ronald_Roos
Try This:
1) Insert a new column and enter this formula to extract the last digit of the number in column D as that is the number that changes:
=RIGHT(D2,1)
2) Then add the CountIF function to the column F. Hide column D if you want to
Jul 03 2019 04:25 AM
Jul 03 2019 05:14 AM