Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Check duplicate rows and keep the last ones.

    ✭✭
    in Formulas and Functions

    Hello,

    Need your help for below problem

    image.png

    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?

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • ✭✭✭✭✭✭
      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.

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭✭

      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

      bassam.khalil2009@gmail.com

      ☑️ 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"

      · Share on FacebookShare on Twitter
    • ✭✭

      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.

      image.png


      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      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.

      · Share on FacebookShare on Twitter
    • ✭✭

      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

      image.png

      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

      image.png

      Any comment?

      Thank you.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      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.

      · Share on FacebookShare on Twitter
    • ✭✭
      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.

      image.png

      Thank you.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭

      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)

      · Share on FacebookShare on Twitter
    • ✭✭

      @CWS_Bob

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

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Thank you. This helped a bunch!

      · Share on FacebookShare on Twitter
    • ✭✭
      Mark Cronk

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Thank you so much, this was so helpful!

      · Share on FacebookShare on Twitter
    • ✭✭✭

      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.

      · Share on FacebookShare on Twitter
    • Community Champion

      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.

      image.png


      Gia Thinh Technology - Smartsheet Solution Partner.
      Email : thinh.huynh@giathinh.tech

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions