Duplicate Row Column Formula?

Sarah123

Is there any way to flag a duplicate (only for the 2nd, 3rd, etc. duplicate)? I'd like to leave the first of an item but flag the multiples of the rows so that those can be deleted. =IF(COUNTIF([Task ID]$1:[Task ID]@row, [Task ID]@row) <> 1, 1, 0) is the formula I'm currently using but I want to have it setup as a column formula so that it'll apply to new rows automatically in my sheet.

Kelly Moore

Hey @Sara123

There is a workaround using the system column [Row ID]. You will need to add it, if you don't already have this column.

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

Would this work for you?

Kelly

Brent Wilson

@Sarah123

Slight problem in Determining the first duplicate and having it be a column formula as you have to look from the first column to the current column so you are always going to have something like

$[Task ID]$1:$[Task ID]@row which cannot be made into a column formula

You can use a column formula to find if there is a duplicate or not.. unfortunately, it cannot determine if it is the first one, or the last one, etc

This formula will identify the duplicates and be made a column formula

=IF(COUNTIF([Task ID]:[Task ID], =[Task ID]@row) > 1, 1, 0)

But cannot highlight the first one

Brent Wilson

@Sara123

I did do this but it requires someone to check the "First Record"

Check a Box based on a checked box in another column

Kelly Moore

Hey @Sara123

There is a workaround using the system column [Row ID]. You will need to add it, if you don't already have this column.

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

Would this work for you?

Kelly

Sarah123

@Kelly Moore that worked perfect! Thank you so much for that idea!

compartido.tactic

There is another way to know which is the first row in a group of rows:

1. given that the group of rows have a common unique identifier in an specific cell (in the formula below is the Project Name)

2.Then by creating an auto-number column [Line Number], each row added will have a sequence number greater than any previous.

3.Then you create a text number column with the column formula:

MATCH([Line Number]@row; COLLECT([Line Number]:[Line Number]; [Project Name]:[Project Name]; [Project Name]@row))

you end having a sequence number for all the rows added that share the same [Project Name].

Hope this helps...

Antonio