Duplicate values identification

Christ.

I know the way to do it for a small table

=if(countif([C1]:[C1],[C1]@row)>1,"Red","")

But that cell reference all the cells in C1. It get to a huge number of reference quickly.

I've reached the 250 000 limit with that and around 4k rows.

Is there a different way that use less cell references ?

Christ.

10 rows = 10^2 reference = 100 references

4000 rows = 4000^2 = 16 000 000 references

Leibel S

@Christ.

Is the data in C1 also a formula?

What kind of formula?

Just the duplicate formula should not give you this error

Christ.

Yes, in this case, it is a formula. Since I want to make sure taht every row is unique, I join 2 column togeter to create an ID. In this case the column are NAME and DATE.

That way, people can only enter one line per name/date. I use it for timesheet.

Leibel S

That still should not give you the error.

Did you try the formula?

Christ.

Yes. It has been running perfectly for 7 months, and then I started getting errors through failled move automation workflow. Something like "the source/destination sheet contains too many references". The only way to make it move the rows was to remove the duplicate detection.

Leibel S

How many rows do you have?

I have sheets with way more complex situations and this is not an issue

Christ.

To be clear, I don't have the issue since I've remove the column formula.

On the destination sheet I have 9196 rows

The source one has now 1692 rows.

I'm transferring everyday everyrow that is at least 90 days old from the source to the destination.