Duplicate Row Column Formula?

Options

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.

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 08/20/22 Answer ✓
    Options

    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

Answers

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    edited 08/20/22
    Options

    @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 C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    edited 08/20/22
    Options

    @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

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 08/20/22 Answer ✓
    Options

    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
    Sarah123 ✭✭✭✭
    Options

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

  • compartido.tactic
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!