5 columns in Sheet 2 Compared to Sheet 1

System

This discussion was created from comments split from: Find Difference between two Cells that could be Multiselect Dropdowns or Text.

Amy Flores

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

=JOIN(COLLECT([MASTER]:[MASTER]))

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

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

Amy Flores

@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)

=JOIN(COLLECT(MONDAY:MONDAY, MONDAY:MONDAY, <>" "), " ")

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

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