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
-
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
-
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.
-
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.
-
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!