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
Best Answers

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

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]
Answers

Join(Collect(Distinct(),Distinct(),Criteria),char(10))
Give that a try. (make sure to text wrap the result)

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.

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

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]
Help Article Resources
Categories
Check out the Formula Handbook template!