How to return the contents of one cell that don't exist in another cell

Alexis R

I have an employee list where I'm tracking training status. I have a column with assigned training, and a column with completed training. I want to be able to display training that an employee has not completed. In the past I've used a JOIN(DISTINCT(COLLECT(CONTAINS formula to pull the training completed but would now like to do the opposite. I want to pull all values in the [Training Assigned]@row that don't exist in the [Training Completed]@row. I tried messing around with a NOT(HAS or NOT(CONTAINS function to no avail. It sounds like I'm looking for is what an IF(NOT(HAS function offers, but to pull whatever doesn't match rather than just being a logical statement. Maybe I'm approaching it wrong so if anyone could point me in the right direction it would be greatly appreciated.

jmyzk_cloudsmart_jp

@Alexis R

I advise against using the Multiple Dropdown list for this type of application. Currently, there is no function or method to convert multiple dropdown list values to a range or split them into individual values*, so handling multiple dropdown lists is very cumbersome. (*Excel has the TEXTSPLIT function, and Google Sheets has the SPLIT function.)

But if you need to use the Multiple Dropdown lists, here is a solution I use.

First, create a separate sheet with the list.

https://app.smartsheet.com/b/publish?EQBCT=5e37120be17e4a67bf6ef3b06f3eacaf

Then, add helper columns to split the multiple dropdown list values in the target sheet. The demo sheet below shows the helper columns TA1 to TA7 and TC1 to TC7.

https://app.smartsheet.com/b/publish?EQBCT=b3ff3f9855a941138b4d2a24924faaf3

Using the following formula, you can split the multiple dropdown list values.

TA1 =IFERROR(INDEX(COLLECT({Training_List}, {Training_List}, CONTAINS(@cell, [Training Assigned]@row)), 1), "")

TA2 =IFERROR(INDEX(COLLECT({Training_List}, {Training_List}, CONTAINS(@cell, [Training Assigned]@row)), 2), "")



TC1 =IFERROR(INDEX(COLLECT({Training_List}, {Training_List}, CONTAINS(@cell, [Training Completed]@row)), 1), "")

TC2 =IFERROR(INDEX(COLLECT({Training_List}, {Training_List}, CONTAINS(@cell, [Training Completed]@row)), 2), "")

Finally, you can get the Training Missing with the following formula.

=JOIN(COLLECT([TA1]@row:[TA7]@row, [TA1]@row:[TA7]@row, NOT(HAS([TC1]@row:[TC7]@row, @cell))), CHAR(10))

The above formula collects a range from split-assigned training whose value does not exist in the range consisting of split-completed training. Then, the JOIN function concatenates the elements of the range.

jmyzk_cloudsmart_jp

Hi @Alexis R

Assuming you have a sheet structure like the second image, you can use ISBLANK(@cell).

=JOIN(COLLECT({Employee_training: 1 Range Assigned}, {Employee_training: 1 Range Completed}, ISBLANK(@cell ), {Employee_training: 1 Range Name}, Name@row), CHAR(10))

https://app.smartsheet.com/b/publish?EQBCT=e1545e9d52bf40da9e02da4762eca6c7

You can also get the list in the Sheet Summary fields.

=JOIN(COLLECT(Assigned:Assigned, Completed:Completed, ISBLANK(@cell), Name:Name, Name#), CHAR(10))

https://app.smartsheet.com/b/publish?EQBCT=7e6d1f2494a44087b1d09f54b6643d25

A different sheet format.

=JOIN(COLLECT(Training:Training, Assigned:Assigned, 1, Completed:Completed, <>1, Name:Name, Name#), CHAR(10))

https://app.smartsheet.com/b/publish?EQBCT=a530e8a6fd69423da06af0d4d53a5c60

Alexis R

Hey @jmyzk_cloudsmart_jp thanks for your response,

The first example you provided appears to do what I need, but I can't seem to get it to work. I'm assuming you're using external sheet references given the { } braces. I think this is where I'm getting lost since I'm just trying to reference the cell contents within the same sheet @row level. Here's what I've attempted:

=JOIN(COLLECT([Training Completed]@row, [Training Assigned]@row, ISBLANK(@cell),[Name]:[Name],[Name]@row),CHAR(10)))

My fault for not providing examples or screenshots of what kind of structure I'm working with. See screenshot below for reference. I do have external sheets of departments with assigned trainings and employee completed trainings but they all get pulled in via formula, which is why I'm trying to keep my new formula native to its sheet.

jmyzk_cloudsmart_jp

@Alexis R

I advise against using the Multiple Dropdown list for this type of application. Currently, there is no function or method to convert multiple dropdown list values to a range or split them into individual values*, so handling multiple dropdown lists is very cumbersome. (*Excel has the TEXTSPLIT function, and Google Sheets has the SPLIT function.)

But if you need to use the Multiple Dropdown lists, here is a solution I use.

First, create a separate sheet with the list.

https://app.smartsheet.com/b/publish?EQBCT=5e37120be17e4a67bf6ef3b06f3eacaf

Then, add helper columns to split the multiple dropdown list values in the target sheet. The demo sheet below shows the helper columns TA1 to TA7 and TC1 to TC7.

https://app.smartsheet.com/b/publish?EQBCT=b3ff3f9855a941138b4d2a24924faaf3

Using the following formula, you can split the multiple dropdown list values.

TA1 =IFERROR(INDEX(COLLECT({Training_List}, {Training_List}, CONTAINS(@cell, [Training Assigned]@row)), 1), "")

TA2 =IFERROR(INDEX(COLLECT({Training_List}, {Training_List}, CONTAINS(@cell, [Training Assigned]@row)), 2), "")



TC1 =IFERROR(INDEX(COLLECT({Training_List}, {Training_List}, CONTAINS(@cell, [Training Completed]@row)), 1), "")

TC2 =IFERROR(INDEX(COLLECT({Training_List}, {Training_List}, CONTAINS(@cell, [Training Completed]@row)), 2), "")

Finally, you can get the Training Missing with the following formula.

=JOIN(COLLECT([TA1]@row:[TA7]@row, [TA1]@row:[TA7]@row, NOT(HAS([TC1]@row:[TC7]@row, @cell))), CHAR(10))

The above formula collects a range from split-assigned training whose value does not exist in the range consisting of split-completed training. Then, the JOIN function concatenates the elements of the range.

Alexis R

I see, that is quite cumbersome. I might have to back to the drawing board for this if that's the case. But I will keep this in mind moving forward, thank you @jmyzk_cloudsmart_jp

jmyzk_cloudsmart_jp

Happy to help!😁 @Alexis R