Comparing two multi-select fields and showing the difference

[Kelly_Vorrasi1]

Is it possible using a formula or automation to compare two multi-select fields and show which items are missing from the 2nd? 

I'd like to have a 3rd field that would show just what was missing from the completed list. 

[Alyssa_Buchthal]

This is very easy to do with a scripting automation. Your trigger would likely be an update to the "Art Complete" field, and you'd loop through the languages requested, checking for each being present in the complete field, and if not passing the final array of those to the "Missing Art" field (or whatever you chose to call it).

Do you have access to scripting steps in automations?

[Kelly_Vorrasi1]

That would be great! I do have access to scripting in automations but I don't have experience writing scripts

[Sho · Accepted solution]

If you just want to text, you can do it in the formula field. Multiselect color will be lost.

REGEX_REPLACE({Languages Requested},SUBSTITUTE({Art Complete},", ","[, ]*|")&"[, ]*","")

[Kelly_Vorrasi1]

That did it! Thank you so much! If it needs to be a multi select for other team members I can have an automation update a separate field

[Kelly_Vorrasi1]

Coming back because this worked so well for this set up, I'm wondering if it's possible to use a similar set up but instead of showing what's missing, can you show what matches? I've been trying to wrap my head around regex but its still beyond my reach. 

So if A says "Pie, Cookies, Ice Cream" and B says "Sandwich, Cookies, Salad, Ice Cream" then C would show "Cookies, Ice Cream"

[Sho]

That's hard to do with a formula field.
You could concatenate those two lists and detect if they were duplicate values, but you'd have a formula like this

REGEX_REPLACE(
"@>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
values&",",
REGEX_EXTRACT(values,"([^,]*,?$?){1}"),"<@"& REGEX_EXTRACT(values,"([^,]*,?$?){1}") & "@>",2),
REGEX_EXTRACT(values,"([^,]*,?$?){2}"),"<@"& REGEX_EXTRACT(values,"([^,]*,?$?){2}") & "@>",2),
REGEX_EXTRACT(values,"([^,]*,?$?){3}"),"<@"& REGEX_EXTRACT(values,"([^,]*,?$?){3}") & "@>",2),
REGEX_EXTRACT(values,"([^,]*,?$?){4}"),"<@"& REGEX_EXTRACT(values,"([^,]*,?$?){4}") & "@>",2),
REGEX_EXTRACT(values,"([^,]*,?$?){5}"),"<@"& REGEX_EXTRACT(values,"([^,]*,?$?){5}") & "@>",2)&"<@",
"@>[^<>@]*<@","")

This formula can detect duplicates up to 5 values.
It can be increased by making the formula longer.

[Angela_Howard]

I'd love to see a formula that compares a Linked record field to a lookup field, and showing what's missing from either!