How to join distinct values from descendant rows of multivalue contact lists

Ken Mullen

In my sheet, the children and further descendant rows each have multivalue contact lists in the "resource" column. I want the parent resource column to list with a semi-colon separator the unique contact names as text or as contacts. Some of the descendant rows have single contacts and others have multiple contacts. Never more than 10 contacts in each row, although the parent summary cell could result in up to 30 contacts (or names as text). There is duplication of contacts between rows which is why I only want distinct value (each contact or contact name as text) appearing once in the parent summary.

Christina09

@Ken Mullen

Not sure if i'm understanding it right. On the parent cell for contacts, you can use this formula:

=JOIN(DISTINCT(CHILDREN()), ":")

This should join only the distinct contacts in the child row with semi colon.

Hope this helps.

Ken Mullen

In my testing, this approach works, but if the children include the same contact in a multi-value list of contacts, the multi-contact values are interpreted as a unique single value and returned. Example:

• Children values: (as multivalue contact lists)
• Ben
• Ben (and) Ken
• Formula results:
• Ben:Ben,Ken

The multivalue contact list format is the issue as it uses a different method (character?) to separate the values.