Removing duplicate names from a cell

1845sarah

Hello

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!

Sarah

Paul Newcome

Try this:

=JOIN(DISTINCT([Dept 1 HOD]@row:[Dept 5 Ops]@row), CHAR(10))

Paul Newcome

Try this:

=JOIN(DISTINCT([Dept 1 HOD]@row:[Dept 5 Ops]@row), CHAR(10))

1845sarah

Thankyou!

Paul Newcome

Happy to help. 👍️