Displaying all unique values on a new sheet from columns on a reference sheet - Help

Bmince

We have a form set up for collecting information from scientists performing runs by different projects. We need to be able to supply material lot information to clients separately by project numbers.

I tried to use the following forums to perform the index/collect/distinct formulas which didn't work for me when referencing a separate reference sheet:
https://community.smartsheet.com/discussion/90798/using-distinct-function#latest
https://community.smartsheet.com/discussion/87186/list-all-unique-values-from-a-column-within-one-smartsheet-in-a-separate-smartsheet

When I would use them as-is (Obviously substituting my references), it would say unparseable or incorrect argument.

I do not need the data to be parsed out into separate rows, it can go into 1 cell. Currently we have been exporting the data to excel and organizing it manually for each project or using macros to do the same work, but would like to automate it in smartsheet since the data is already parsed into smartsheet from the scientists.

To add a layer, I need this function to only work if a specific Project # is found. Sometimes the cells include multiple projects:

Current set-up:
Main sheet collected by a form used as reference sheet: Lot Documentation

Secondary sheet to organize data: Project Lot Documentation

So for example, one of the material lots captured is water on the reference sheet "Lot Documentation". The exact name of the column is "Ultrapure Water Lot".
On the Project Lot Documentation sheet, If the project column is "INFCS", I want all unique lots of water to be listed where INFCS is at least 1 of the projects in the "Project #" column on the reference sheet. I will have to repeat this process for 100s of unique materials and repeat this project lot sheet for each project.

If we can automate this, it will save a lot of time. Any help is appreciated.

Bmince

More info:
I managed references to the other Lot Documentation sheet for some materials. "Count" includes the row IDs that auto populate.

Brian_Richardson

I'm not 100% sure I understand the explanation, but here's a solution with a couple of assumptions.

Assumptions

• The Lot Documentation sheet has the following columns
  • Ultrapure Water Lot: with a single numeric lot value like 92004
  • Project #: a multiple-selection dropdown column with project ids like INFCS
• The Project Lot Documentation sheet has the following columns
  • Project #: a text/number column with a single value like INFCS
  • Water Lots: a multiple-selection dropdown column listing all water lots from Lot Documentation sheet found where Project # matches

Formula

If the above setup is true, then in the Water Lots column in the Project Lot Documentation sheet, you can use this formula:

=JOIN(COLLECT({Ultrapure Water Lot}, {Project #}, HAS(@cell, [Project to Check]@row)), CHAR(10))

When entering the formula, click Reference Another Sheet to insert the cross-sheet references for {Ultrapure Water Lot} and {Project #}, select the appropriate column from your Lot Documentation Sheet, and give the reference the appropriate name for the column that you're referencing.

Make this formula a column formula by right clicking and choose Convert to Column Formula.

This formula collects a list of Ultrapure Water Lot numbers where the Project # column has the Project # from the row of the Project Lot Documentation sheet.

Also, for readability you can also make Water Lots column word-wrapped, which will place each number on a separate row. CHAR(10) is a line feed character and is also the character that tells a multiple-selection dropdown column to consider each value as a separate selection, rather than a string of text.

You can also play around with the formula a little bit, for example if you need to export the Line Feed CHAR(10) won't export well, so you could add a column with the same formula but "," as the delimeter. You can replace JOIN with COUNT to count the results, etc.

Bmince

Hi Brian, thank you for the reply. Unfortunately, the formula was unparseable for me and when trying to change to column it states that the syntax isn't right.

To clarify assumptions -

Assumptions

• The Lot Documentation sheet has the following columns
  • Ultrapure Water Lot: with a single numeric lot value like 92004
  • Project #: a multiple-selection dropdown column with project ids like INFCS
  • Includes all projects and all lots of all materials for all projects.
• The Project Lot Documentation sheet is a blank sheet so far - where I am trying to organize and add formulas referencing the Lot Documentation sheet to organize data by a specific project (like INFCS) filtering out the multitude of other data not related to INFCS.

Brian_Richardson

Check to make sure you typed it in exactly or copy/paste - the formula structure is correct so if you're getting UNPARSEABLE then you may have missed a comma or added an extra parenthesis or something.

Also make sure your references are in there correctly, not just text copied and pasted, but actually setup with those names, or replace the reference names with your own references to the columns.

The formula assumes some column names that I listed in "Assumptions", so be sure to alter it if you're using different column names than what I guessed at.