# Formula Help - Index(Distinct(Collect

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

• 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.

• ✭✭✭✭✭

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))
```
• Thank you! That worked! I greatly appreciate your help.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!