Formula Error

Rob B

I am trying to highlight and mark duplicate records with the newest Created Date, but I am getting this error...

#Incorrect Argument

for this formula...

=IF(COUNTIFS([Order/Work Order ID]:[Order/Work Order ID], [Order/Work Order ID]:[Order/Work Order ID], [Order/Work Order ID]@row, [Created Date]:[Created Date], @cell < [Created Date]@row) > 0, 1, 0)

Not sure what is wrong?!

Rob B

Hi @Frank B.

Thanks for that! It set me on the right path. Here is what I ended up with

=IF(COUNTIFS([Order/Work Order ID]:[Order/Work Order ID], [Order/Work Order ID]@row, [Created Date]:[Created Date], <[Created Date]@row) > 0, 1, 0)

I'll use SS automation to move any rows with "1" to a new sheet

Frank B.

The issue with your formula is with the condition @cell < [Created Date]@row. This condition is causing an error because @cell refers to the cell in the column you're applying the conditional formatting to, but there is no [Created Date] column in that range.

Here's an updated formula that should work:

=IF(COUNTIFS([Order/Work Order ID]:[Order/Work Order ID], [Order/Work Order ID]@row, [Created Date]:[Created Date], ">"&[Created Date]@row) > 0, 1, 0)

This formula will count the number of times the current row's Work Order ID and Created Date appear in the range [Order/Work Order ID]:[Created Date] where the Created Date is greater than the Created Date in the current row. If this count is greater than 0, then the formula will return 1, indicating that the current row is a duplicate. If the count is 0, then the formula will return 0, indicating that the current row is not a duplicate.

You can apply this formula to the column you want to format as duplicates, and set the format style to highlight duplicates.

Rob B

Hi @Frank B. ,

Thanks for the quick response. Unfortunately now I am getting #UNPARESEABLE for an error.

Frank B.

Hi @Rob B Try updating the formula to the following:

=IF(COUNTIFS([Order/Work Order ID]:[Order/Work Order ID], [Order/Work Order ID]@row, [Created Date]:[Created Date], ">"& [Created Date]@row) > 0, 1, 0)

Note the addition of a space after the "&" operator to separate it from the date value. This should resolve the #UNPARSEABLE error.

LMK!

Rob B

Hi @Frank B.

Thanks for that! It set me on the right path. Here is what I ended up with

=IF(COUNTIFS([Order/Work Order ID]:[Order/Work Order ID], [Order/Work Order ID]@row, [Created Date]:[Created Date], <[Created Date]@row) > 0, 1, 0)

I'll use SS automation to move any rows with "1" to a new sheet