3

I have some product codes, some of which are numbers with leading zeroes, stored in a spreadsheet.

I'm doing some operations that rely on a COUNTIF not equal to function (details in comments but not relevant to the question). Countif seems to break when dealing with strings containing numbers with leading zeroes.

For a simple demonstration

For example if A1 contains "foo" then:

=COUNTIF(A1,"<>"&A1)

returns 0. That is to say, there are no cells in A1 that are not equal to A1 (of course).

But if A1 contains "0001234" as text, then it returns 1, i.e. A1 IS NOT EQUAL TO A1

Further info: =countif(A1:"<>"&"1234") returns 0, so the problem looks to be that excel autmatically converts the string "0001234" to the number 1234, but only in the criteria and not in the range and then finds "0001234"=/=1234.

WORKAROUND: create a helper column with a junk text character appended to all codes. I currently have added a helper column containing ="@"&X1 to all my lists, but it's not an elegant solution.

Some_Guy
  • 744
  • 3
  • 12
  • 36
  • shouldn't the formula be something like `=Countif(A1:A100,"<>"&A1)`? – Scott Craner Aug 30 '16 at 16:21
  • Are you trying to identify all products with leading zeroes? – Raystafarian Aug 30 '16 at 17:33
  • Hi there ray, always good to see you on a question. The actual function I was using is =COUNTIFS(A:A,A1,B:B,"<>"&B1)<1 Which returns FALSE if there is a record with the same identifier in A but a different value in B. B should be unique to A (but not vice versa) in this data, I'm doing some error checking. (i.e. product 2345 could have attribute 001. There may be other products with atribute 001 too in the list, but 2345 should never have both attribute 001 and 002) – Some_Guy Aug 31 '16 at 11:01
  • I didn't include this in the question as I thought it might obfuscate the actual problem I'm facing of countif's behaviour. The task I'm performing may have a more elegant solution than mine, but at the end of the day my formula does do what it should (except with codes with leading 0s). I have a workaround, but it seems worthwhile to have this problem "on the record" as it were. – Some_Guy Aug 31 '16 at 11:01
  • Sometimes VBa is a better choice, but I'm assuming that's not an option for you? – Dave Sep 01 '16 at 11:42

1 Answers1

2

EDIT: to change from adding NUL to adding * to the criteria.

You will see similar behavior with Booleans. One way I have found to "force" COUNTIF to not do the conversions is by adding (or pre-pending) a * to the criteria argument. Eg:

=COUNTIF(A1,"<>" & A1 & "*" )

will return 0 even with the string 0001234 in A1

The "*" forces COUNTIF to treat A1 as text in the criteria argument. This will fail, of course, if A1 contains a number. So you might need to test for that

=COUNTIF(A1,"<>" &  A1 & IF(ISNUMBER(A1),"","*"))

And here is a screen shot using the data you posted in your comment:

enter image description here

Since using COUNTIF in this way is not completely bulletproof, you could also consider the EXACT function.

=N(NOT(EXACT(A2,B2)))

or even, more simply, if case sensitivity is not an issue:

=A2<>B2

will return the same results as COUNTIF with the wildcard in the criteria, and also be able exclude the possibility of the range being tested appearing within the criteria.

enter image description here

If I understand the COUNTIFS formula you posted in a comment above, the following might provide the equivalent, but I would like to see data with your expected results.

=SUMPRODUCT((A:A=A2)*(B:B<>B2))<1

Also, the formula would work much more efficiently (quickly), if you could limit the whole column references to being less than a whole column.

Ron Rosenfeld
  • 8,198
  • 3
  • 13
  • 17
  • This seems to just always return 0, even if things aren't equal. See attached image http://imgur.com/a/6VSYF – Some_Guy Aug 31 '16 at 11:16
  • @Some_Guy You are correct, of course. Don't know why I thought it was working. Will Edit to the correct solution. – Ron Rosenfeld Aug 31 '16 at 11:27
  • This is better, however it relies on a wildcard being used. With where you can guarantee one item will contain another item, but if I had one code 0002345 and another code 0002345A this would cause a problem. Definitely an improvement on the previous situation though. – Some_Guy Sep 01 '16 at 09:51
  • 1
    @Some_Guy Yes, that will be a limitation. Whether it is fatal depends on your database -- only you can judge that. If you are not wedded to using `COUNTIF`, there are other solutions. I will post one. – Ron Rosenfeld Sep 01 '16 at 10:09