Duplicates in formula-generated column

KellyMart

I use formula =IF(COUNTIF(Column:Column, Column@row) > 1, 1) to find duplicate values within a column. However, this formula does not work if the value of the source column is generated by a formula. For Instance, ColumnC = ColumnA + ColumnB. The above referenced formula does not work when trying to find duplicates in ColumnC. Is there any way to accomplish this?

Carson Penticuff

I set up a duplicate of your exact sheet and was able to duplicate your results. I'm not exactly sure how/why, but there appears to be some confusion when parsing entries in your Device ID column as text vs. numbers. I was able to get it working by wrapping VALUE() tags so that all would be treated as numbers. Hopefully it will work for you as well.

=IF(ISBLANK([Device ID]@row), 0, IF(COUNTIF([Device ID]:[Device ID], VALUE(@cell) = VALUE([Device ID]@row)) > 1, 1))

KellyMart

Thanks Carson. That did the trick; and I learned something new! Much appreciated.

Carson Penticuff

Your formula should work even in the scenario you are describing. In your example, would you be dealing with numbers/text/symbols? Just curious if this could be formatting related.

I set up a quick example and it is working:

In this example for formulas are as follows:

ColumnC: =ColumnA@row + ColumnB@row

ColumnD: =IF(COUNTIF(ColumnC:ColumnC, ColumnC@row) > 1, 1)

KellyMart

Thanks for the reply, Carlson. All fields contain Text/Number values. A per the screenshots below, A and C are Text/Number and B is Dropdown list. All rows in the screenshot below should be marked as duplicate except for the 3rd and 4th rows.

Formula for Device ID=IF([Loc ID]@row = "", "", LEFT([Loc ID]@row, 4) + ID@row)

Formula for DupIDSW=IF(ISBLANK([Device ID]@row), 0, IF(COUNTIF([Device ID]:[Device ID], [Device ID]@row) > 1, 1))

Carson Penticuff

I set up a duplicate of your exact sheet and was able to duplicate your results. I'm not exactly sure how/why, but there appears to be some confusion when parsing entries in your Device ID column as text vs. numbers. I was able to get it working by wrapping VALUE() tags so that all would be treated as numbers. Hopefully it will work for you as well.

=IF(ISBLANK([Device ID]@row), 0, IF(COUNTIF([Device ID]:[Device ID], VALUE(@cell) = VALUE([Device ID]@row)) > 1, 1))

KellyMart

Thanks Carson. That did the trick; and I learned something new! Much appreciated.