Listing Distinct values from a contact List column

Hi all,

Please help! :-) I'm trying to get a list of distinct values from a contact list type column but what feels like it should be simple keeps stumping me. In the attachment, I have two columns on the Left (Type and Owner) and would like to locate the distinct owner values and then count the number of instances in the Type column.

Any points would be gratefully received.

Thanks


Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi Andrew,

    Using formula to create a list of distinct values listed in different rows can't be done.

    You'll need to use another approach. Consider inserting a new text/ number column [owner-type] with the formula:

    =countifs(owner:owner, owner@row, type:type, type@row)

    The response will be the number will be what you're looking for.

    To create a table you'll need to enter the owner names in rows manually. Then use countifs formulas to get the number of types for that owner.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi Andrew,

    Using formula to create a list of distinct values listed in different rows can't be done.

    You'll need to use another approach. Consider inserting a new text/ number column [owner-type] with the formula:

    =countifs(owner:owner, owner@row, type:type, type@row)

    The response will be the number will be what you're looking for.

    To create a table you'll need to enter the owner names in rows manually. Then use countifs formulas to get the number of types for that owner.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Thank you Mark

    That's great and I'll use your suggestion.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Thanks Andrew. Appreciate you contributing to the community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • SJ Sellers
    SJ Sellers ✭✭✭✭

    I have a sheet where I am using this formula:

    =INDEX(DISTINCT({Assignee}), Sequence@row)

    To return the distinct list of values in a Contact List from another sheet. It is working on one sheet, but I am getting #INVALID DATA TYPE in the other.

    Any ideas what is going on?

  • SJ Sellers
    SJ Sellers ✭✭✭✭

    Hi everyone - I figured out what was going on - I had a very large SmartSheet and one of the rows had a value that was NOT a ContactList value - It was simple text.

    Here's the relevant text from the function notes: DISTINCT can only reference one data type at a time. For example, if the referenced range includes text values and numeric values, you'll see an INVALID DATA TYPE error. (or in my case, Contact List values and TEXT values)

  • I am trying to get a list of distinct customer from a Case Tracking sheet the customer is a Drop-down column type and the the formulae I am using is =DISTINCT({Case_Client})

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!