Formula to Check Duplicates on a MultiSelect Drop Down
To evaluate each value within a multivalue cell is what has me stumped.
'CountIf' exceeding 1 is the key for single values. I thought about using 'contains'.
I'm curious if anyone has figured this out.
Best Answer

Thanks for thinking about this with me Jackie.
Referencing a multiselect cell with multiple values in a formula converts all of the values to a single string.
It's an edge case. The API is the ticket on this one .
Answers

Have you tried the CountIFS formula? it allows for several variables.
If you are looking for duplicates for just a single column, you could try applying a filter or sorting the column.
Jackie Ziemke, Marketing Director
ruralMED Management Resources

Hi Jackie,
Yes, the countif function, which I understand works for single value cases was my first thought. How can we do it formulaically for multiple values in one cell in a multiselect dropdown column?
Evaluating each value in the cell rather than all of the contents of the cell as one value.
Jeremiah

There is a "Countifs" formula with an "s" at the end. Here is a link to the formula: https://help.smartsheet.com/function/countifs?frame=0&nav=1
It takes the range, and if it meets multiple criteria, then it will count it.
For example, this formula is handy if you are looking for all of the new assets assigned to a certain employee. So the variable (rows) would have to have to contain "new" and the "employee name" in another column.
If you are looking to count a sum of variables. For example you want to count all assets that are "new" and "Used" but not "broken", I just did a test and you can layer the "=countif" formula to get a count of all the variables that meet the first condition added to the variables that meet the second condition.
Example: =COUNTIF([column2]:[column2], "new") + COUNTIF([column2]:[column2], "used")
So if there are 5 "new" and 6 "used" you will get a total of 11!
Is this something that you are looking for? If not, can you provide me a little more information and how your sheet is set up for me to see if I can help?
Jackie Ziemke, Marketing Director
ruralMED Management Resources

I see. Rather than adding multiple criteria, which the CountifS solves I am trying to look at each value within the cell and evaluate it individually.
Maybe a better question is how do we access single values within a multiselect dropdown cell?
I'm asking about finding the Duplicate 'A' in this case:
I've published the sheet here
This is a better example of the challenge:
There is a duplicate 'A'.

I think I found something! I didn't know about this formula: "HAS" I kept the formula counting the "A"s in the Check Duplicates column.
Here is the formula:=COUNTIF([Multiple Criteria]:[Multiple Criteria], HAS(@cell, "A"))
Jackie Ziemke, Marketing Director
ruralMED Management Resources

Yes. We can search them all for "A" by hard coding A as the criterion.
The trick is to replace the statically written "A" criterion with the values of the multiselect.
The real question: How do we access and evaluate each value in an multiselect cell with a formula?

So are you assigning a separate value to each "criterion" for example: A=1, H=2? or are you wanting to to specifically find the amount of duplicates within a column?
Can you provide me with an example of what you want your end result to be?
Jackie Ziemke, Marketing Director
ruralMED Management Resources

Thanks for thinking about this with me Jackie.
Referencing a multiselect cell with multiple values in a formula converts all of the values to a single string.
It's an edge case. The API is the ticket on this one .

To make sure this isn't going over my head, you are wanting the formula that checks a cell, and if the cell has multiple valuables (drop down select), you would like them to be combined in a way that they become a single line of text versus two separate selections?
Jackie Ziemke, Marketing Director
ruralMED Management Resources

My goal is to determine if each value in each multivalue cell is unique among all of the values in all the multivalue cells in the column.

I have a feeling like our brains work on different wavelengths and we are learning how to speak each other's language. So I appreciate your patience! My competitive nature is causing me to "need" to figure this out.
So you are wanting a "trigger" that says whether things have a match or are unique, when compared to all of the values within the cell, and if it is unique to create to create a single string of values?
Jackie Ziemke, Marketing Director
ruralMED Management Resources

Lol. Yes
Using a formula in Smartsheet the isolated, multiple values in a multiselect cell are evaluated as a single string. Combining them into a single string value, loosing their individuality making it impossible to evaluate each value without some intense regular expressions, if it's even possible to ascertain a delimiter. This one is probably going to remain unsolved for a minute.

I ended up trying something. I made all of the valuables into a single string, then I made a formula column where it counts the number of occurrences for each of the variables that are duplicates for the multiselect cell at that row.
If there is a "1" it is "unique" if it is larger than "1" it has duplicates. With the numbers, you could then have a trigger "check box" that marks things as unique. You could also highlight using conditional formatting for those that are unique.
Jackie Ziemke, Marketing Director
ruralMED Management Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives