Removing duplicate names from a cell



I have a form where Users select a Department via a checkbox. This populates the intake sheet, of which the Department Managers names are populated via an INDEX MATCH into a Dept HOD column: the Dept Ops name is also populated in this way.

All of the names are then pulled into an 'Invitee' column via this formula:

=JOIN([Dept 1 HOD]@row + CHAR(10) + [Dept 1 Ops]@row + CHAR(10) + [Dept 2 HOD]@row + CHAR(10) + [Dept 2 Ops]@row + CHAR(10) + [Dept 3 HOD]@row + CHAR(10) + [Dept 3 Ops]@row + CHAR(10) + [Dept 4 HOD]@row + CHAR(10) + [Dept 4 Ops]@row + CHAR(10) + [Dept 5 HOD]@row + CHAR(10) + [Dept 5 Ops]@row)

What I want to do, is eliminate any duplicate names which appear in the 'Invitee' column.

Does anyone have any ideas on how I can do this?

Many thanks in advance!


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!