Work around for duplicates to only show the most updated row



I have a list of names that were entered to the sheet more than once (but a different value in other columns).

I have a helper column to identify which are the duplicates, but is there a way to show only one of the updated one in a report?

Source Sheet

Test 1 & Test 2 are duplicates, but I only need the newer enter to show up in a report.

Is there a way to work around it?

Thank you for your help!


  • Maaik Meijerink
    Maaik Meijerink ✭✭✭✭✭

    Hello Christina,

    If you mean only the newer based on the Start Date, of course you can: you can use a column formula based on the MAX of the start date and the range of the specific Name via the COLLECT formula.

    =IF(MAX(COLLECT([Start Date]:[Start Date]; Name:Name; Name@row)) = [Start Date]@row; 1)

    And of course in your report filter on the flagged ones.

    Please also check the screenshot.

    Grtz. Maaik

  • Christina09
    Christina09 ✭✭✭✭✭✭

    @Maaik Meijerink

    Thanks for your quick response. This works, but if I want to do the same thing but in a column with ID#, the one that started with 0's, appears to be #INVALID OPERATION. is there a formula that works both for number and text?

    Thank you so much! :)

  • mragans23

    Hi Christina, I would love to know how you was able to get checkbox column to work as a formula, thanks.

  • Christina09
    Christina09 ✭✭✭✭✭✭


    I added a helper column for ID# since the formula won't match if it's a number. =VALUE([ID#]@row)

    =IF(MAX(COLLECT([Start Date]:[Start Date]; [ID# Helper]:[ID# Helper]; [ID#]@row)) = [Start Date]@row; 1)

    Hope that helps.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!