CODEWITHSUNDEEP
excel
Colin Shields
Colin Shields

Reputation: 3

How to find duplicates when value is not equal to text?

So... this is going to be a difficult explanation...

I am using a barcode scanner to enter a 20 digit number into Excel. Due to the limitations of Excel this has to be done as text, since Excel only allows up to 15 digits.

This means that my cell has a shown value of example 00257108402007839772 but the value "behind" the cell is 257108402007839000 (the last 3 digits are turned to zeros).

I have to check the values for duplicates, and the last digits are in most cases the digits that differentiate the numbers from each other. This means that when doing a conditional formatting on the cells, the most part of the numbers are flagged as duplicates.

So my question is basically - do you know of a way to search duplicates on the shown value, and not on the "real" value of the cell?

I hope this makes sense....

Upvotes: 0

Views: 293

Answers (1)

JvdV
JvdV

Reputation: 75990

Here is something for you to consider:

enter image description here

As you can see COUNTIF() does not work on these values, but SUMPRODUCT() will when we just directly compare a range against a single cell. Below you'll see an example of conditional formatting:

enter image description here

Rule used on range A1:A4: =SUMPRODUCT(--($A$1:$A$4=$A1))>1

Upvotes: 2

Related Questions