Find First Duplicate in a Sheet

Andrew Brimer · 05/29/19

I am trying to find the first duplicate value with the purpose of using that to highlight another cell; however, my attempts at the first part of the process have resulted in both #UNPARSEABLE and #INVALID COLUMN VALUE.

The formula I'm trying to use mimics something I've used successfully in Excel:

=COUNTIF($[Master Project]$1:$[Master Project]1, $[Master Project]1) = 1

Thanks in advance for assistance.

Andrew

Andrée Starå · 05/29/19

Hi Andrew,

Try something like this.

Add the formula to the first cell in a checkbox column and then drag fill down.

=IF([Master Project]@row= ""; 0; IF(COUNTIF($[Master Project]$1:$[Master Project]1; $[Master Project]1) = 1; 1))

The same version but with the below changes for your and others convenience.

=IF([Master Project]@row= "", 0, IF(COUNTIF($[Master Project]$1:$[Master Project]1, $[Master Project]1) = 1, 1))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrew Brimer · 05/30/19

Hi Andrée,

It worked. Thank you for the assistance with the formula!

Best regards,

Andrew

Andrée Starå · 05/30/19

Excellent!

Happy to help!

Best,

Andrée

Stephanie Jordan · 02/25/23

@Andrée StaråI used this as example to build my formula and it worked perfectly!

=IF([Trainee Email]@row = "", 0, IF(COUNTIF($[Trainee Email]$1:$[Trainee Email]1, $[Trainee Email]1) = 1, 1))

Any thoughts on how to apply this as a column formula? I know the cell / absolute references are throwing it off but everything I've tried just isn't working. Appreciate the guidance.

tunesdoomz · 02/25/23

Thank you for the assistance with the formula!

Jeff Grimes · 06/01/23

I've used the formula provided by@Andrée Staråabove to identify a row with a new unique value in a specific column with a check mark so that the row can be copied to a separate sheet.

=IF([Assignment Reference]@row = "", 0, IF(COUNTIF($[Assignment Reference]$1:$[Assignment Reference]1, $[Assignment Reference]1) = 1, 1)).

Why can't this formula be used as a column formula? My master sheet will continue to grow with new unique cases, so dragging the formula down isn't sustainable.

Thank you!

Kelly Moore · 06/01/23

Hey@Jeff Grimes

You cannot use absolute identifiers or row numbers in column formulas

If you are wanting to identify the first instance (ie, distinct) of a value, an alternative approach uses a system column like Row ID or Date Created (assuming rows are added chronologically down a page). This can be done as a column formula. I'll assume you have the system auto-number [Row ID] column in your sheet

IF(COUNTIFS([Assignment Reference]:[Assignment Reference], [Assignment Reference]@row, [Row ID]:[Row ID], @cell<=[Row ID]@row) = 1, 1)

Will this work for you

Kelly

Jeff Grimes · 06/01/23

@Kelly Moore, thanks for the help! That worked!

I am dealing with the issue of the box being checked for the first blank row, but I think I can address that in my automation to ensure the blank row is not pulled over.

Jeff Grimes · 06/01/23

@Kelly Moore,

I revised my automation to ignore instances where the box is changed to checked, but "Assignment Reference" blank, when copying a row over to the other sheet, but I'm now running into a snag of my automation not triggering because the checkbox in the first blank row is already checked, when entering a new/unique instance of an Assignment Reference into the first available blank row. The checkbox column in these instances are not "changing" to checked, they're just remaining as checked.

Is there anything that can be done to the below formula to ensure that it is not checking the box for the first blank row as a unique case?

=IF(COUNTIFS([Assignment Reference]:[Assignment Reference], [Assignment Reference]@row, [Entry ID]:[Entry ID], @cell <= [Entry ID]@row) = 1, 1)

Kelly Moore · 06/02/23

Hey@Jeff Grimes

Will this work? If not, adjust the name of the column for the blank. I assumed it was the [Assignment Reference] field.

=IF(COUNTIFS([Assignment Reference]:[Assignment Reference], [Assignment Reference]@row, [Entry ID]:[Entry ID], @cell <= [Entry ID]@row,[Assignment Reference]:[Assignment Reference],<>"") = 1, 1)

Kelly

Jeff Grimes · 06/02/23

@Kelly Moore, that did the trick! Thanks so much for the help!

Kelly Moore · 06/02/23

Glad to help. Have a great weekend

Find First Duplicate in a Sheet

Comments

Help Article Resources

Categories