Formula to Check Duplicates on a Multi-Select Drop Down

JeremiahHorstick · 10/22/20

To evaluate each value within a multi-value 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.

JeremiahHorstick · 10/22/20

Thanks for thinking about this with me Jackie.

Referencing a multi-select 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 .

Jackie Ziemke · 10/22/20

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.

JeremiahHorstick · 10/22/20

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 multi-select dropdown column?

Evaluating each value in the cell rather than all of the contents of the cell as one value.

-Jeremiah

Jackie Ziemke · 10/22/20

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?

JeremiahHorstick · 10/22/20

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'.

Jackie Ziemke · 10/22/20

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"))

JeremiahHorstick · 10/22/20

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 multi-select cell with a formula?

Jackie Ziemke · 10/22/20

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?

JeremiahHorstick · 10/22/20

Thanks for thinking about this with me Jackie.

Referencing a multi-select 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 .

Jackie Ziemke · 10/22/20

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?

JeremiahHorstick · 10/22/20

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

Jackie Ziemke · 10/22/20

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?

JeremiahHorstick · 10/22/20

Lol. Yes

Using a formula in Smartsheet the isolated, multiple values in a multi-select 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 un-solved for a minute.

Jackie Ziemke · 10/22/20

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 multi-select 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.

Formula to Check Duplicates on a Multi-Select Drop Down

Best Answer

Answers

Categories