Compare two cells with comma separated values and return the unique values from the comparison?
I am trying to compare two cells with comma separated values in Smartsheet and return only the unique values. See below for an example:
In excel I am able to accomplish this by using the following formula =TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A1:B1), ", ")),,1)) Is there any way to do something similar to this in Smartsheet?
Thanks!
Answers

You can use a combination of
JOIN
,COLLECT
, andFIND
functions. Smartsheet doesn't have an exact equivalent to Excel'sTEXTJOIN
orUNIQUE
functions, but you can construct a formula that emulates the desired behavior.Here's an example formula structure:
=JOIN(COLLECT(Column1:Column1, FIND(",", @cell) > 0), ", ")
You'd need to expand on this logic to compare the two cells and filter out only the unique values. However, Smartsheet formulas currently do not support array operations or the direct equivalent of Excel's
UNIQUE
function, making this a complex task that might require multiple helper columns or manual intervention.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 200 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!