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

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!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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.

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin
    Answer ✓

    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
    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 :-)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!