Listing Distinct values from a contact List column

Andrew Foster

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

Mark Cronk

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

Mark Cronk

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

Andrew Foster

Thank you Mark

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

Mark Cronk

Thanks Andrew. Appreciate you contributing to the community.

Mark

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

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)

debojyoti

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