Remove duplicate formula has changed and now need to be corrected.

Mark Stacey

This is the formula to remove a duplicate that is already on a report:

The number circled in read was not part of the original formula and I don't know why it is there. I have found a duplicate on the report where before all duplicated were being removed. Does anyone know why this has happened and how to correct this?

Darren Mullen

@Mark Stacey Did you check the cell history and see if anyone edited that cell?

Mark Stacey

Checked the cell history and nothing was edited.

Paul Newcome

Most likely the sheet was sorted.

To avoid this, you can use an auto-number column (with no special formatting) and convert it into a column formula.

=IF(COUNTIFS([Auto-Number]:[Auto-Number], @cell<= [Auto-Number]@row, [NJUNS Tcket#]:[NJUNS Ticket#], @cell = [NJUNS Ticket#]@row)> 1, 1)

Mark Stacey

Thanks Paul! What is meant by 'sorted'? and why would that happen?

Paul Newcome

If someone sorted the sheet, it could move what was originally the top row to further down in the sheet. Since you were referencing that specific cell, the cell reference would have been updated.