Distinct values from multiple sheets

KevinMio8

I currently have 14 sheets with the following columns:

Batch # and Reviewer

1. I use an Index Distinct formula to acquire the unique batch numbers from all 14 sheets and put them into 14 columns on the 'metrics' sheet.
2. I then use another index distinct to get a list of all the unique batch numbers into one 'Unique Batch Number' column on the metrics sheet.
3. I then use a join distinct collect to find all reviewers that are listed as reviewers for the batch from #2 above and put their names into another 14 columns on the metrics sheet.

So now the issue I have is I would like to list all distinct viewers for one batch number. In the scenarion I have listed above, I may have the same reviewer appear by itself or in a 'collection' in one cell for step 3 and when I try to join distinct collect the names from step 3 I get duplicates (I am guessing because of multiple names appearing in one cell from the previous join distince collect).

The real question is - how can I get distinct names from multiole sheets with a criteria that they reviewed a unique batch number. (Example Below for 3 sheets):

Sheet1			Sheet 2			Sheet 3
Batch#	Reviewer		Batch#	Reviewer		Batch#	Reviewer
ABC	Bob		ABC	Bob		ABC	Mary
DEF	Mary		DEF	Mary		DEF	Mary
GHI	Tim		GHI	Bob		GHI	Bob
JKL	Jane		JKL	Jane		JKL	Mary
MNO	Jane		MNO	Bob		MNO	Tim
Result Required
ABC	Bob Mary
DEF	Mary
GHI	Tim Bob
JKL	Jane Mary
MNO	Jane Bob Tim

Neil Egsgard

@KevinMio8 ,

I recently did something similar for a couple of sheets but I needed the values as contacts. It is simpler if you don't need contacts. My solution based on your example sheets was:

For text names, not contacts
Columns and formulas on "Result Required" sheet.

Column: Sheet 1 reviewers
Type: Dropdown multi select
Formula
=SUBSTITUTE(JOIN(DISTINCT(COLLECT({Sheet1-Reviewer}, {Sheet1-Batch#}, [Batch#]@row)), CHAR(10)), ",", CHAR(10))

Column: Sheet 2 reviewers
Type: Dropdown multi select
Formula
=SUBSTITUTE(JOIN(DISTINCT(COLLECT({Sheet2-Reviewer}, {Sheet2-Batch#}, [Batch#]@row )), CHAR(10)), ",", CHAR(10))

Column: Sheet 3 reviewers
Type: Dropdown multi select
Formula
=SUBSTITUTE(JOIN(DISTINCT(COLLECT({Sheet3-Reviewer}, {Sheet3-Batch#}, [Batch#]@row )), CHAR(10)), ",", CHAR(10))

Column: Reviewers summary
Type: Dropdown multi select
Formula
=JOIN(DISTINCT(COLLECT([Sheet 1 reviewers]@row :[Sheet 3 reviewers]@row ,[Sheet 1 reviewers]@row :[Sheet 3 reviewers]@row , <>"")))

The substitutes formulas in the "Sheet # reviewers" columns were because I sometimes had multiple values separated by a comma because the source columns were Contact columns. The substitute with CHAR(10) = <character return> allows each value to appear as a separate entry in the dropdown multiselect.

Extra steps to get Contacts in a Contact column
To have the results end up as Contacts:

1. Python script that creates a Smartsheet User list everyday with emails. Runs as a schedule task on a server.
2. DataShuttle to load the user list into a Smartsheet DataTable daily
3. DataTable Connection to populate user list into a sheet called "User List"
4. Cross sheet formula on "Results required" sheet pulls data from "User list" sheet to "Results required" sheet
   1. Join Collect uses dropdown names from "Reviewers Summary" column to get emails from "User list"
5. DataMesh to transfer text emails from "Results required" sheet to "Results required" contact column
   1. This converts the emails to Contacts
      

I hope that helps.

Neil Egsgard
Business Solutions Architect
Southern Alberta Institute of Technology