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.

Join Collect formula that excludes the current row from the results

I'm trying to create a formula that creates a list of duplicates in my sheets, I've gotten this formula to work but it's showing the original row within the list of duplicates. I tried adding an auto number column and including a condition that the list shouldn't include the current row. However, the addition of this to the formula has broken my formula and I'm now getting #unparseable error.

Here's the current code I'm using, I've put in bold the condition that's currently breaking the formula.

=IF([# of Duplicates]@row > 0, JOIN(COLLECT(RowID:RowID, [10-Digit Mat #]:[10-Digit Mat #], [10-Digit Mat #]@row, Row#:Row#, @cell <> Row#@row), ", "))

Has anyone else experienced this issue? If so what would you change so that the formula isn't angry anymore?

Best Answer

  • Community Champion
    Answer ✓

    If the RowID you already have is unique and is the thing you want to exclude, could you just add the criteria that the rowID is not the rowID on the current row? This part in bold:

    =IF([# of Duplicates]@row > 0, JOIN(COLLECT(RowID:RowID, [10-Digit Mat #]:[10-Digit Mat #], [10-Digit Mat #]@row, RowID:RowID, <>RowID@row), ", "))

    Does that do the trick?

Answers

  • Community Champion
    Answer ✓

    If the RowID you already have is unique and is the thing you want to exclude, could you just add the criteria that the rowID is not the rowID on the current row? This part in bold:

    =IF([# of Duplicates]@row > 0, JOIN(COLLECT(RowID:RowID, [10-Digit Mat #]:[10-Digit Mat #], [10-Digit Mat #]@row, RowID:RowID, <>RowID@row), ", "))

    Does that do the trick?

  • ✭✭✭✭

    @KPH, Thanks! Idk why I hadn't thought of that but that's perfect and that worked!

  • Community Champion

    Wonderful. Sometimes you just need a second pair of eyes!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions