Conditional Formatting Duplicate Values and SUMIFS() false data, need help

[supershot0015]

Hi there,

I can not determine what my issue is, but I need a solution fast!!

We Concatenate() 4 data points to creat a 17 digits text field for lookup. In using conditional formatting for duplicate values and a Sumifs() formula for totalling dollars, it is treating "09961254123882001" & "09961254123882002" as the same number. It is showing up as a duplicate value and the total is being double counted as the Sumifs() is returning the same value for both.

Thanks in advance!

Brent

 

[Jonmo1]

Excel is a 'numbers' based program.
So it often considers anything that looks like a number as a number.
However, it only sees 15 significant digits.

So it's really only seeing the first 15 digits of each value, which are actually the same.
099612541238820|01
and
099612541238820|02


What you might do, is in your concatenation of 4 data points, insert an actual text value, like the pipe character |

=A1&"|"&B1

This will force the sumif to treat the values as TEXT instead of Number.

 

[supershot0015]

Thanks Jonmo, I figured it was some sort of number vs text related issue, but wasn't sure. Much Appreciated and very timely!

 

[Jonmo1]

Glad to help, thanks for the feedback.