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

Gulshan Sharma

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

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]

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]