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?

Find more posts tagged with

Sheets

Formulas

Join

collect

IF(

Accepted answers

KPH

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?

All comments

KPH

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?

Matthew L

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

KPH

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.