Formula Help - Index(Distinct(Collect

System

This discussion was created from comments split from: Extracting a list of unique value from reference sheet based on some conditions.

Candy Vonk

Hi,

I'm trying to do something similar to this situation. I'm new to Smartsheets and especially these formulas; I can't figure out where this result information is held?

1+count(Result$1:Result1)

I can get the first row to work fine using this formula:

=INDEX(DISTINCT(COLLECT({Owner POC}, {Owner POC}, <>""), {Owner POC}), 1)

Which gives me the results I expected to see. I can change the 1 to a 2 and get the next owner. But I would like to automate the filling in of unique cells from the Smartsheet into a new list completing the entire list. I tried to use the second index but can't get the formula to work. I've tried so many combinations that I'm getting myself confused.

Here is the formula I tried that is similar to the one in this post:

=INDEX(DISTINCT(COLLECT({Owner POC}, {Owner POC}, <>""), {Owner POC}), 1+count({Owner POC}$1:[{Owner POC}1,"")

Any help would be appreciated.

Thanks in advance!!

Devin Lee

Hey @Candy Vonk

The simple way would be to use a helper column and input the numbers 1,2,3 etc and reference that cell via helper@row. If you don't want that just reference another column that is already there and use the rows to count. Unfortunately, you will get a circular reference if you attempt to count the row the formula is in so just count any other one.

=INDEX(DISTINCT(COLLECT({Owner POC}, {Owner POC}, <>""), {Owner POC}), COUNT(Primary$1:Primary@row))

Candy Vonk

Hi @Devin Lee

Thank you! That worked! I greatly appreciate your help.