Finding duplicates

SamprasFederer

Finding duplicates so that I can use conditional formatting to highlight the duplicates.

Screenshot 2024-08-12 at 22.18.40.png

Why am I not getting the 2 in the "find duplicate" column? I also tried =if(countif([ref. no.]:[ref. no.],[ref. no.]@row) >1,1). what am I doing wrong?

Thank you in advance,

RDRGSJ00

Hello SamprasFederer,

The issue you are having is that Smartsheets adds an apostrophe to the front of 0's, so 020496 is actually '020496 so it is treating it as a text string (click in the cell and verify). If it is possible you can a helper column and use the VALUE function like this:

=VALUE([Ref. No.]@row)

This takes the value in [Ref. No.] column and turn it into a number. As you can see below:

The first two columns are a mock-up of what you have [Ref. No.] & [Duplicates] and 020496 is not being flagged, the second two columns [Ref No Num] and [Ref Num Duplicates] is using the VALUE function and it converted 020496 to 20496 and it flagged it as a duplicate.

I hope this helps.

RDRGSJ00

Hello SamprasFederer,

The issue you are having is that Smartsheets adds an apostrophe to the front of 0's, so 020496 is actually '020496 so it is treating it as a text string (click in the cell and verify). If it is possible you can a helper column and use the VALUE function like this:

=VALUE([Ref. No.]@row)

This takes the value in [Ref. No.] column and turn it into a number. As you can see below:

The first two columns are a mock-up of what you have [Ref. No.] & [Duplicates] and 020496 is not being flagged, the second two columns [Ref No Num] and [Ref Num Duplicates] is using the VALUE function and it converted 020496 to 20496 and it flagged it as a duplicate.

I hope this helps.

SamprasFederer

Now I understand, thank you!

Love Smartsheet and its community!!!

Donna Beckmann

I was having the exact same issue as SamprasFederer with the apostrophe. I solved it by simply adding an = in front of the column@row. So the formula ended up being =COUNTIF([Ref. No.]:[Ref. No.], =[Ref. No.]@row)