Distinct Values across 2 sheets
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)?
Best Answer
-
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.
Answers
-
Here is a similar question that included two possible solutions.
https://community.smartsheet.com/discussion/97956/finding-distinct-values-from-two-different-sheets
-
@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.
-
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.
-
This worked - thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!