How to remove duplicate strings in two columns (Two adjacent cells) then add into another

Joe Suh
Joe Suh ✭✭
edited 05/25/21 in Formulas and Functions

Column 1

Dog Cat Mouse


Column 2

Dog Cat Pig


Column 3 (result)

Dog Cat Pig Mouse

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Joe Suh

    There isn't one direct function that can easily do this, but there are potential formula solutions that we could build out for a similar result.

    How many values do you have in your actual Column 1 and Column 2? Are these set values to be selected every time, or could new values be added in each row?

    The reason I ask is because if you knew your set values for each of the columns, you could build a long IF statement to return a value IF it is contained in either of the columns.

    Ex:

    =IF(OR(CONTAINS("Dog", [Column 1]@row), CONTAINS("Dog", [Column 2]@row)), "Dog") + IF(OR(CONTAINS("Cat", [Column 1]@row), CONTAINS("Cat", [Column 2]@row)), " Cat") + IF(OR(CONTAINS("Mouse", [Column 1]@row), CONTAINS("Mouse", [Column 2]@row)), " Mouse") + IF(OR(CONTAINS("Pig", [Column 1]@row), CONTAINS("Pig", [Column 2]@row)), " Pig")


    Does that make sense? Would this be feasible with your current columns and values?

    Cheers!

    Genevieve

  • Joe Suh
    Joe Suh ✭✭

    My God this is the most unsmart way... we have multiple lists that are in excess of 30 lines.. I will pass.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!