How to return a join without a specific value?

Faaez Kamaal

Hi guys, been racking my brain all morning on this one. I have a column with an array of names, and another column with singular names. I'm looking to compare the 2 columns, remove the matching name, then return the remaining names.

My full formula will contain matching on multiple criteria, the name matching as described above and if 2 date values match. I got these working independently, just can't quite wrap my head around the removal of the matched value and returning the remainder.

Faaez Kamaal

Got a little closer.

=SUBSTITUTE([All FT]@row, [Technician Today]@row, "")

This returns the remaining values minus the matched name. However, it's not delimited and yet another challenge.

And closer still:

=SUBSTITUTE(SUBSTITUTE([All FT]@row, CHAR(10), ","), [Technician Today]@row, "")

However, this is returning a pair of commas ( , ) with the blanked value.

Faaez Kamaal

This is the solution I ended up using:

I created a column with all the names I need to know about, then I made a checkbox column with the following formula to check if they are in the list.

=IF(NOT(ISBLANK(Technician@row)), IF(CONTAINS(Technician@row, [Technician Today]:[Technician Today]), 0, 1))

Technician Today is being pulled from this

=JOIN(DISTINCT(COLLECT([FT-Technician]@row, [FT-Scheduled Date]@row, [Today Helper]@row)), ",")

FT-Technician is being pulled from this

=INDEX({FT-Tracker-Tech}, MATCH([FT-Tracker-RowID]@row, {FT-Tracker-RowID}, 0))

And finally, FT-Tracker-RowID is being pulled using the Comma helper trick I learned some time ago.

=JOIN(DISTINCT(COLLECT({FT-Tracker-RowID}, {FT-Tracker-RowID}, @cell <> "", {FT-Tracker-ScheduledDate}, [Today Helper]@row)), ",") + ","

=IF(Comma@row = "", "", IFERROR(LEFT(String@row, FIND(",", String@row) - 1), ""))

I feel that there is a lot of calculations happening for such a simple task, so I'll leave this thread as unanswered if someone more clever than I can come up with a cleaner solution.

Kelly Moore

Hey @Faaez Kamaal

When you say remove the matching name, do you mean to remove it from one of the columns? If yes, can you do this using "Clear Cell Value" automation? Or, replace the duplicate name with another value using "Change Cell Value" so these could be excluded from your Collect? If I understood your post correctly you already have the duplicates flagged - the flagging could become the trigger for the automation.

Would that work for you?

Kelly