Return all unique values by Location
![Pestomania](https://us.v-cdn.net/6031209/uploads/userpics/FZ1TVQVTSDAG/nBGLU8MMMM78J.jpg)
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.
Best Answer
-
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), "")
Answers
-
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.
-
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:
-
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), "")
-
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.
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives