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, [10Digit Mat #]:[10Digit Mat #], [10Digit 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

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, [10Digit Mat #]:[10Digit Mat #], [10Digit Mat #]@row, RowID:RowID, <>RowID@row), ", "))
Does that do the trick?
Answers

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, [10Digit Mat #]:[10Digit Mat #], [10Digit 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!

Wonderful. Sometimes you just need a second pair of eyes!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!