Join Collect formula that excludes the current row from the results

Matthew L

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?

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?

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!