Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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, and FIND functions. Smartsheet doesn't have an exact equivalent to Excel's TEXTJOIN or UNIQUE 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions