Distinct Values across 2 sheets

hdierkers

Hello,

I have 2 separate sheets, and am trying to calculate the distinct values across the 2, knowing that there may be duplicates between them. In my example, I have a project list that is pre-smartsheet, and another list that is post-smartsheet (we do not need or want to combine). How can I calculate distinct client names between the 2?

My formula for distinct values in one sheet is: =(COUNT(DISTINCT(COLLECT({LEGACY Submissions Project Client Name}, {Legacy Project List Status}, ="Completed"))), but how do I add to this number the distinct values for Client Names in another sheet without duplicating the numbers/information from the original (ie for repeat clients)?

Carson Penticuff

I would recommend creating a new "helper" sheet to act as an intermediary for the information. It would look like this:

Instead of using autonumber here, just add enough rows to be sure there will always be enough, it is not going to hurt to have too many. 5000? Sure... Then, just drag down the numbers.

A couple stand-ins for your source sheets:

You will have to tailor this a bit for your setup, but the formulas for the helper sheet:

List 1 - Column formula

=IF([Number]@row <= COUNT(DISTINCT(COLLECT({LEGACY Submissions Project Client Name}, {Legacy Project List Status}, ="Completed"))), INDEX(DISTINCT(COLLECT({LEGACY Submissions Project Client Name}, {Legacy Project List Status}, ="Completed")), [Number]@row), "")

List 2 - Column formula

=IF([Number]@row <= COUNT(DISTINCT(COLLECT({OTHER Submissions Project Client Name}, {OTHER Project List Status}, ="Completed"))), INDEX(DISTINCT(COLLECT({OTHER Submissions Project Client Name}, {OTHER Project List Status}, ="Completed")), [Number]@row), "")

Total Distinct - Not a column formula

=COUNT(DISTINCT([List 1]:[List 2]))

You can then use cell linking in your destination cell to link back to the Total Distinct cell.

Carson Penticuff

Here is a similar question that included two possible solutions.

https://community.smartsheet.com/discussion/97956/finding-distinct-values-from-two-different-sheets

hdierkers

@Carson - how do I get this information to auto-populate from the source sheets, into a different column/sheet to determine the distinct values? I was using an INDEX formula with auto-populated number rows, but the sheet I am trying to pull the INDEX information in doesn't have as many rows as the source sheets.

Carson Penticuff

I would recommend creating a new "helper" sheet to act as an intermediary for the information. It would look like this:

Instead of using autonumber here, just add enough rows to be sure there will always be enough, it is not going to hurt to have too many. 5000? Sure... Then, just drag down the numbers.

A couple stand-ins for your source sheets:

You will have to tailor this a bit for your setup, but the formulas for the helper sheet:

List 1 - Column formula

=IF([Number]@row <= COUNT(DISTINCT(COLLECT({LEGACY Submissions Project Client Name}, {Legacy Project List Status}, ="Completed"))), INDEX(DISTINCT(COLLECT({LEGACY Submissions Project Client Name}, {Legacy Project List Status}, ="Completed")), [Number]@row), "")

List 2 - Column formula

=IF([Number]@row <= COUNT(DISTINCT(COLLECT({OTHER Submissions Project Client Name}, {OTHER Project List Status}, ="Completed"))), INDEX(DISTINCT(COLLECT({OTHER Submissions Project Client Name}, {OTHER Project List Status}, ="Completed")), [Number]@row), "")

Total Distinct - Not a column formula

=COUNT(DISTINCT([List 1]:[List 2]))

You can then use cell linking in your destination cell to link back to the Total Distinct cell.

hdierkers

This worked - thank you!