How do I identify duplicate rows?

Mike B · 04/25/23

How can I identify duplicate rows in a column by adding an additional column to identify the duplicate. numbering the dupes 1,2,3 etc.

Sample data:

Row PK#

Engg.FY23O&M012

Engg.FY23CIP012

Engg.FY21CIP001

Engg.FY23O&M012

Engg.FY23CIP012

Engg.FY21CIP001

Paul Newcome · 04/27/23

You need two different columns. An auto-number type column called "Auto", and a text/number column with the previous formula in it.

Paul Newcome · 04/25/23

Are you able to provide a screenshot with the expected output manually entered for reference?

Mike B · 04/25/23

Sure:

Paul Newcome · 04/26/23

You will need to insert an auto-number column with no special formatting (called "Auto" in this example) then use this:

=COUNTIFS(Auto:Auto, @cell<= Auto@row, [PK#]:[PK#], @cell = [PK#]@row)

Mike B · 04/26/23

Creating the Auto number column, I'm not allowed to enter a formula. Is there a setting that needs to be changed?

Paul Newcome · 04/26/23

The formula would go in the Dupe column.

Mike B · 04/26/23

I tried this in a column named Auto

=COUNTIFS(Auto:Auto, @cell <= Auto@row, [PK#]:[PK#], @cell = [PK#]@row)

Mike B · 04/26/23

I'll change the name back to Dupe

Mike B · 04/26/23

Results:

Paul Newcome · 04/27/23

You need two different columns. An auto-number type column called "Auto", and a text/number column with the previous formula in it.

Mike B · 04/27/23

Thanks you so much!!!!! It worked.

Paul Newcome · 04/27/23

Happy to help. 👍️

Best Answer