5 columns in Sheet 2 Compared to Sheet 1


  • Amy Flores
    Amy Flores ✭✭
    edited 11/26/21

    I am also looking for something like this, however I have 1 Master list (On one sheet that can be modified over time when needed) and 5 columns that each need to be compared to the MASTER list. Both lists are from single select TEXT (if that makes a difference)

    My thoughts so far;

    On my master list sheet in a single cell have the formula


    On my second sheet in hidden helper cells for Col1, Col2, Col3, Col4, Col5

    =JOIN(COLLECT([Col1]:[Col1])) and so on for each of the other Col2, Col3, Col4, Col5

    I'm stumped as where to go from here.

    I want everything that was collected from Col1/2/3/4/5 (respectively) to be compared and deleted from the MASTER, giving me the delta - the goal is that there is 0, but need to know which ones are missing if any.

    Not sure if I am making more or less work for myself going this way, but thoughts on how to accomplish this would be wonderful.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Amy Flores Are you able to provide screenshots for reference?

  • @Paul Newcome

    I was able to put together a working formula based on 3 different answers you provided to 3 separate posters here in the SmartSheet Community.

    So HUGE THANK YOU for that!!👏👌

    To populate my Master List

    =JOIN(COLLECT([Employee Name]:[Employee Name], [Employee Name]:[Employee Name], <>""), " ")

    To populate who was used for the day(Col1,Col2,Col3)


    To find who was missing from the list

    =SUBSTITUTE($Day$1, [Missing Mon]$2, "")

    To say that I was elated when I figured it out, feels like an understatement. I was in shock😲 and then, just buzzing from the inside out. 😁

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Amy Flores I'm glad you were able to get it working. Well done.👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!