Check duplicate rows and keep the last ones.

Hello,

Need your help for below problem

The item A and B are duplicated at the last two rows. I want to keep the last ones and remove the rows that checked in the "Duplicate" column. Can you help develop the formular for the "Duplicate" column?

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    You'll need to add a date criteria to know which entry is oldest. Do you have a created column? You could also use an autonumber column.

    Try:

    IF(COUNTIFS(Item:Item; items:item; Item@row; created:created; @cell<created@row) > 0; 1; 0)

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Thinh Google

    Hope you are fine, if you want to remove the duplicated rows which checked the check box in the Duplicate column you can do that by using workflow automation to move those rows to archive sheet.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hi @Bassam Khalil ,

    I am sorry that i did not raise the problem clearly.

    What I want is the formula in Duplicate cell to mark the item is duplicated if does exist the same item in the Item column.

    Thank you.


  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Good morning,

    Try:

    IF(COUNTIF(ITEM@row:item, =item@row)>1, 1, 0)

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Hi Mark,

    I tried and it worked, with a small modification : IF(COUNTIF(Item@row:Item10; Item@row) > 1; 1; 0)

    Need to add a specific ID at the end of Item to make it work. In this case, I set Item@row:Item10

    In fact, the Item range can reach thousands, so it shouldn't be limited at this specific Item10.

    If I set Item@row:Item , it showed error #UNPARSABLE

    Any comment?

    Thank you.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    You'll need to add a date criteria to know which entry is oldest. Do you have a created column? You could also use an autonumber column.

    Try:

    IF(COUNTIFS(Item:Item; items:item; Item@row; created:created; @cell<created@row) > 0; 1; 0)

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Thinh Google
    Thinh Google ✭✭
    edited 05/16/21

    Hi @Mark Cronk,

    I added an ID auto number and modified your formula : IF(COUNTIFS(Item:Item; Item@row; ID:ID; >ID@row) > 0; 1; 0) and it did work.

    Thank you.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Excellent. Great solution. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • CWS_Bob
    CWS_Bob ✭✭✭

    Thanks for this! Note that the syntax for the formula. For me it worked with commas and not semi-colon :=IF(COUNTIFS(Item:Item, Item@row, ID:ID, >ID@row) > 0, 1, 0)

  • @CWS_Bob

    Yes, you're right. The syntax for formula is varied by region. Semi-colon works in Vietnam, my country.

  • Thank you. This helped a bunch!

  • This actually does work, but there is an extra row reference in the formula. Also the criteria is incorrect on the last argument.

    Original:

    IF(COUNTIFS(Item:Item; items:item; Item@row; created:created; @cell<created@row) > 0; 1; 0)

    Fixed:

    IF(COUNTIFS(Item:Item; Item@row; created:created; <created@row) > 0; 1; 0)

    This will give you a "1" for all of the duplicates after your oldest. If your trying to clear out all but the newest duplicates, flip the carrot for created@row.

    IF(COUNTIFS(Item:Item; Item@row; created:created; >created@row) > 0; 1; 0)

    I have an automation running now that will move all but the youngest duplicate to an archive sheet.

  • Thank you so much, this was so helpful!

  • Spoke too soon! What if the duplicates are created and/or modified on the same date :(

    I'm having a really hard time working this out.

    Cannot convert the cell formula to a column formula for unique row ID, so this hinders the progress I thought I had. And this one does not work because of the dupe dates as well.


    What else can be used to identify duplicates, that can be converted a column formula so I do not have to maintain.

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭

    Hi veronica,

    Use an ID auto number to make rows unique and this formula : IF(COUNTIFS(Item:Item; Item@row; ID:ID; >ID@row) > 0; 1; 0) and it did work.



    Gia Thinh Co. - Smartsheet Solution Partner.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!