Duplicate Values: =If(Countif doesn't work with concatenated formula.

nbers

I am trying to identify duplicate values in a column titled [Program ID]. [Program ID] is the result of a concatenate formula: =[Site Num]@row + "." + ProgNum@row The result of the above formula is a decimal number like 7.1, 8.4, 50.3, etc.

I am using the following formula in a helper column to identify duplicates in the [Program ID] column: =IF(COUNTIF([Program ID]:[Program ID], [Program ID]@row) > 1, "Duplicate", "Unique")

The helper column only returns "Unique" even if duplicates are inserted in the [Program ID] column as seen below. However, If I change the '"." to a "-" in the concatenate formula, the =IF(COUNTIF formula works!

Thanks for your help!!

NicoLHC

@nbers for me its working .

=IF(COUNTIF([Program ID]:[Program ID], [Program ID]@row) > 1, "Duplicate", "Unique") maybe you need to add a value in case some are text.

nbers

Is your Program ID column generated by concatenate formula with a dot? The IF(COUNTIF formula works when the Program ID is manually entered but not when Program ID is the result of a formula UNLESS I change the "." to a "-".

Is there a way to tell if something is entered as a number or text like in excel?

NicoLHC

@nbers i just added manual, yes its the same like in excel sometimes you need to format back to value.

nbers

Edit: I built a Test grid from scratch but identical the grid giving me issues and all formulas work perfectly. Do you know if it's possible a grid to become buggy and simply not work?

Perhaps i'm not following? The If function works fine when Program ID is manually entered - it does not work when program ID is generated by formula.

How can I format back to value? I started a new grid from scratch and I'm still having this issue.

Nic Larsen

I was curious about this one so I also tested and it is working for me as well. Are your column types set to Text/Number?

You can amend your one formula to output the value by doing this:

=VALUE([Site Num]@row + "." + ProgNum@row)

One other option on the other formula I use to find duplicates, It'll just count how many times it finds it:

=COUNTIFS([Program ID]:[Program ID], FIND([Program ID]@row, @cell) > 0)

nbers

Hi Nic,

Yes, All columns are set to Text/Number.

When I ammend the formula with =Value in my live sheet, I get an #INCORRECT ARGUMENT SET error. I do not get this error in my test sheet. This is what made my wonder if my live sheet is somehow corrupted.

The COUNTIFS formula returns seemingly random integers unrelated to the actual count.

I'm happy to share access to this sheet if that's allowed. Me and the other person in my office that understands Smartsheet are stumped.

NicoLHC

@nbers feel free to share the sheet to me nico.roepnack@lighthouseconsultings.com

nbers

@NicoLHC Just want to let you know that I shared the sheet with you. Thanks again!

NicoLHC

@nbers got it