Extracting a list of unique value from reference sheet based on some conditions

Hello there,

I have been trying to extract unique values from a different sheet. Suppose there is a sheet with Project Name, Project Owner and Project Type. I want to extract a list of Unique Project Owners from the other sheet who manages same Project Type. Here is an example data:

I am expecting these two results after applying the formula for both categories:

I have tried Distinct with Collect. This seems fine if I need to count these value but not when I need the list. Can anyone please help me with your expertise.

Thanks

Find more posts tagged with

Accepted answers

L_123

For different cells use an index. It will be 2 formulas

index(Collect(Distinct(),Distinct(),Criteria),1) -will return the first result

iferror(index(Collect(Distinct(),Distinct(),Criteria),1+count([first result]$1:[first result]1),"") - will be your dragdown for the rest

Gulshan Sharma

Thanks @L@123

I got the result what I needed.

Here is the formula used to get the result:

=INDEX(DISTINCT(COLLECT(Range, Criteria Range 1, Criteria 1, Criteria Range 2, Criteria 2)), 1) [ first cell]

=INDEX(DISTINCT(COLLECT(Range, Criteria Range 1, Criteria 1, Criteria Range 2, Criteria 2)), 1+count(Result$1:Result1),"") [following cells]

All comments

L_123

Join(Collect(Distinct(),Distinct(),Criteria),char(10))

Give that a try. (make sure to text wrap the result)

Gulshan Sharma

Hi @L@123

Thank you for the response. I am able to get these values within one cell using

JOIN(DISTINCT(COLLECT(Range, Criteria range1, Criteria 1)), CHAR(10))

However, I am looking for a way to get these values in different cells so that I can create a summary to show in dashboard.

L_123

For different cells use an index. It will be 2 formulas

index(Collect(Distinct(),Distinct(),Criteria),1) -will return the first result

iferror(index(Collect(Distinct(),Distinct(),Criteria),1+count([first result]$1:[first result]1),"") - will be your dragdown for the rest

Gulshan Sharma

Thanks @L@123

I got the result what I needed.

Here is the formula used to get the result:

=INDEX(DISTINCT(COLLECT(Range, Criteria Range 1, Criteria 1, Criteria Range 2, Criteria 2)), 1) [ first cell]

=INDEX(DISTINCT(COLLECT(Range, Criteria Range 1, Criteria 1, Criteria Range 2, Criteria 2)), 1+count(Result$1:Result1),"") [following cells]

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.