Do leading zeroes affect "duplicate flag" formulas?

Draykov

I have a sheet with a column of the 'Text/Number' type called "ID Number." My intent is to have another column of the 'Checkbox' type (called "Flag Duplicate") to show at a glance that an "ID Number" has been duplicated (the data in the "ID Number" row should be unique in each cell within the column).

I used a column formula for "Flag Duplicate" which initially seemed to work:

=IF(COUNTIF([ID Number]:[ID Number], [ID Number]@row) > 1, 1)

If I manually punch in a number in the "ID Number" cell on, say Row 1 and then do the same for the corresponding cell for Row 2, it works. Both "Flag Duplicate" cells in each row show a flag...UNLESS...the ID Number has a leading zero. And I have a lot of ID numbers with leading zeros. :(

Is there some alteration to the formula that needs to take place in order for this to flag duplicate ID Numbers correctly? Or maybe some alteration to the column type?

Thanks!

Paul Newcome

Leading zeros will affect this because that changes it from numerical data to a text string. Now you have two different data types within the range, and that throws the COUNTIFS off every time.

Insert a helper column (can be hidden after setting up) and use this to convert every row into a text string.

=[ID Number]@row + ""

plus quote quote

Then reference this helper column in your COUNTIFS.

Paul Newcome

Try an @cell reference in your COUNTIFS.

=IF(COUNTIFS([Column Name]:[Column Name], @cell = [Column Name]@row)> 1, 1)

Paul Newcome

Leading zeros will affect this because that changes it from numerical data to a text string. Now you have two different data types within the range, and that throws the COUNTIFS off every time.

Insert a helper column (can be hidden after setting up) and use this to convert every row into a text string.

=[ID Number]@row + ""

plus quote quote

Then reference this helper column in your COUNTIFS.

Draykov

Thanks, Paul. So, in that scenario, what type of column would I use for the helper? Another Text/Number?

Paul Newcome

Yes. A text/number column. The formula will only output text strings. Some may look like numbers, but if you have no formatting, you will notice the original column will have those with leading zeros left justified and those without leading zeros right justified (text vs numbers) whereas the helper column will all be left justified.

Draykov

Thanks, Paul. So far so good. So I now have a helper column that seems to be converting my ID Numbers to string text using: =[ID Number]@row + ""

Now...what should I do to the formula in my "Flag Duplicate" column in order to identify duplicates in the helper column?

I tried converting...

=IF(COUNTIF([ID Number]:[ID Number], [ID Number]@row) > 1, 1)

...to...

=IF(COUNTIF([ID Helper]:[ID Helper], [ID Helper]@row) > 1, 1)

While that new formula was accepted without error, I seem to have lost what functionality I had (i.e. identifying duplicate ID numbers that did not have leading zeroes).

Paul Newcome

Are you able to provide a screenshots for context?

Draykov

Paul Newcome

Try an @cell reference in your COUNTIFS.

=IF(COUNTIFS([Column Name]:[Column Name], @cell = [Column Name]@row)> 1, 1)

Draykov

🤦🏼‍♂️ OOPS!

When I converted the formula for the helper column, I unintentionally dropped the @cell (I want to say I had that in one of my working versions, contrary to what's in the screenshot). Adding it back appears to have enabled my intended functionality.

You're my hero once again, @Paul Newcome! Always appreciate your timely and effective assistance!

Paul Newcome

Happy to help. 👍️