Display list of Distinct values from a column in Sheet A into Sheet B

NishaTKD

My Sheet A has a contact column that is multiple select and a cell have multiple names within that column. How do I display only distinct values from a contact columns in Sheet A into a column in Sheet B?

Thanks in advance!

Genevieve P.

Hi @NishaTKD

Thank you for the screen captures, this definitely helps!

No, there currently isn't a way for a formula to read a column and parse out individual items down multiple rows. You could use my suggestion above to bring in the Distinct values into one cell, but then you'd need to check that against your Team Member list and manually add in the value(s) missing.

Genevieve P.

Hi @NishaTKD

There currently isn't a way to look into a multi-select Contact column and pull back distinct contacts into another sheet based on the entire column.

What you could do is translate your Contacts in the source sheet into a multi-Select field (using just the Display Name instead of the Contact value).

Then you could use a JOIN cross-sheet formula to return all the distinct values from this Multi-Select column into one Multi-Select cell in your Sheet B:

=JOIN({Multi Select Column}, CHAR(10))

Cheers,

Genevieve

NishaTKD

@Genevieve P. Thank you for responding to my query. Let me elaborate my screnario: Sheet A has the names of the team members and the date when they are available for next few days.

I would like to see in Sheet B count of days a team member is available as shown in below screenshot.

I am currently manually adding names in sheet B when a new team member name is added in Sheet A. So I was wondering if there is a way to add/show distinct names from Sheet A into Sheet B without doing it manually. I understand DISTINCT function cannot be utilized. Is there any other option?

Genevieve P.

Hi @NishaTKD

Thank you for the screen captures, this definitely helps!

No, there currently isn't a way for a formula to read a column and parse out individual items down multiple rows. You could use my suggestion above to bring in the Distinct values into one cell, but then you'd need to check that against your Team Member list and manually add in the value(s) missing.

NishaTKD

Thank you @Genevieve P. for your reply. I will try your suggestion and will hope that in future the capability is available to use DISTINCT in a way I desired to use :-)