Return all unique values by Location

Pestomania

I am hoping that this is possible:

I would like to look at Sheet "BIA All Data" and return all "Process Names" that match the "Site Name".

For Test Site, there are Process 1 & Process 2 in the BIA Process Name field.

=INDEX(COLLECT({BIA Process Name}, {BIA Site Name}, [Site Name]@row), 1)

I would prefer that I could insert "Site Name" in the first row and have a formula that returns ALL Process Names that match that Site.

Paul Newcome

In that case you would need a text/number column (called "Number" in this example) manually populated wiht the numbers one through however many you think the maximum will be (plus a few extra just in case).

Then you would use this:

=IFERROR(INDEX(DISTINCT(COLLECT({BIA Process Name}, {BIA Process Name}, @cell <> "", {BIA Site Name}, @cell = [Site Name]@row)), Number@row), "")

Paul Newcome

You would use a JOIN function in place of the INDEX function and the 1 at the end would be replaced with your delimiter of choice.

Pestomania

https://community.smartsheet.com/discussion/comment/444530#Comment_444530

Hi Paul,

That works to give them all to me in a cell. I didn't clarify properly.

I am looking to have them in their own row.

This is what I would like it to do:

Paul Newcome

In that case you would need a text/number column (called "Number" in this example) manually populated wiht the numbers one through however many you think the maximum will be (plus a few extra just in case).

Then you would use this:

=IFERROR(INDEX(DISTINCT(COLLECT({BIA Process Name}, {BIA Process Name}, @cell <> "", {BIA Site Name}, @cell = [Site Name]@row)), Number@row), "")

Pestomania

https://community.smartsheet.com/discussion/comment/444550#Comment_444550

That did the trick.

Just to confirm, I cannot make an absolute reference to [SiteName]@1 for a column formula, correct? That would be awesome if I could similar to excel.

Paul Newcome

That is correct. You cannot use an absolute reference in a column formula.

But…

You can reference a sheet summary field. If you want the selection made in a cell in the sheet, you can use a cell reference in a sheet summary field and then reference this sheet summary field in a column formula.