Returning Non Duplicates when comparing two Multi-Select Columns

Debbie Sawyer

Dear Community

Is this possible in Smartsheet? I just can't seem to return what I need!

I've been playing with Join(Collect()) type statements but maybe I have overcomplicated it!

Scenario and requirements are explained in the screen shot.

Happy for you to say Column 1, Column 2 and Column 3 in any suggested formula, as my column headings here are just for explanation purposes.

Thank you!

Paul Newcome

You would need a second sheet that has each of the selectable codes (1st column) listed in their own cells going down a column. Then you would use a JOIN/COLLECT/NOT/HAS combo to pull from the second reference sheet any cell NOT/HAS 1st column "@cell".

Debbie Sawyer

@Paul Newcome

Thanks Paul

Yes, that is the combination of functions that I was trying with a separate Master Ref list that has the codes listed out individually. But I couldn't get it to work.

Hmmm... I'll go back and try again if you think it should be possible.

Thank you!

Debbie Sawyer

@Paul Newcome

This returns incorrect argument set.

I can usually work out complex formulae (!) but I am going around in circles on this one! ha ha - seems so logical too. Have I done a glaringly obvious omission somewhere?

Paul Newcome

@Debbie Sawyer Of course ranges / column names will need updated as needed, but here's the syntax...

=JOIN(COLLECT([All Possible Selections]:[All Possible Selections], [All Possible Selections]:[All Possible Selections], NOT(HAS([Selected Options]@row, @cell))), CHAR(10))

Debbie Sawyer

@Paul Newcome

Thank you for the syntax.

That works but only if the Initial Codes Selected (1st column) were all of the possible codes. In my actual scenario there are 850 codes, where someone will select say, 5 of them. The 5 selected codes (from the possible 850) will be duplicated across 2 columns. (as shown in the image) The Initial Codes Selected are the invitees to an event and the Codes Editable is showing the accepted invitees. The invitees that are removed from the second list need to be known. So in comparing the second column back to the first you can see which ones have been removed, as they are the non duplicates from the first column. The syntax shared here does show the one that is missing between the two columns, but also shows all the codes that weren't selected in the first place as well.

I really need to compare Multi Select against Multi Select! :D

There is a workaround, on the original master list I could add columns to count how many times each code was invited to an event and then count how many times they didn't accept, then we would know how many events they didn't go to... Hmmm - that might be a workaround. But it wouldn't tell me which events they didn't attend. I'd have to create reports that filter out where they were in column 1 but not column 2. I'd prefer to return the codes of the ones that weren't there.

I can get the syntax to compare the two Multi's and identify when they are not the same, but the returned value is all the values in column 1 (the cell contents) instead of just the one value that is missing. I sort of need a HAS() type function on the first argument of the SELECT statement.

Any further thoughts?

Paul Newcome

Ah. I didn't realize the initial column was also variable.

I am assuming no one will be selecting all 850 possible codes in the first column. What is the max number? We could potentially use helper columns to parse out the initial selections on each row and then use these helper columns as the "All Possible Selections" range in the solution I provided. I have a (relatively) easy way of parsing we could use if the number of helper columns is feasible.

Debbie Sawyer

@Paul Newcome

I had thought about hidden columns with ticks etc but there are too many variables in play in the main sheet too, it would cause the sheet to be too big too soon.

No worries. I have gone with Plan B workaround of comparing # invites with # participating and using a formula to workout # rejections. Then we will just need to plug in some filtered views into the Smarter Dashboards that we are going to use on this project to allow the user to look up the codes and find out the names that way.

A bit long winded and not as user friendly, but will do the job.

Thanks for engaging in the thread and confirming that I was not going completely crazy but not being able to return the value I was trying to return! ha ha

Paul Newcome

We wouldn't need ticks though. Even though there are 850 possible selections, if users will never select more than 10, we would only need 10 helper columns. We would be able to parse out each selection into columns labeled "Selection 1", "Selection 2", "Selection 3", etc.. They would be very generic column headers and the data populated in them would be dynamic.

The below is a good example of a "dynamic" list of selectable options on each row. This one goes out to 5 selections possible to show that we only need as many helper columns as will accommodate the maximum number of selections on a single row.

Debbie Sawyer

Ahh - I see what you are doing Paul - nice approach.

Sadly for this scenario they could be choosing hundreds of options from the list (potentially even all of them at one point or another) so I'll stick with the workaround.

But really appreciated your thoughts on this. 😊

Paul Newcome

Oh wow. Yeah. That's a little more than a sheet could handle. Hahaha. This is actually something I have been trying to figure out for a while. I may just have to revisit this one now.

Debbie Sawyer

Ohh - well, I am all ears if you get something working! 🤩