How to receive the differences between 2 multiple select formated fields?

Topic Labels: Automations Formulas
395 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

The situation is as follow, I have two multiple select formatted fields in Airtable, namely "Placement(Planned)" where I'd like to insert placements to place my content and "Placemen (Used) are the placements where I already used the content. Please advise if it possible to have in the field "Placement (Stayed)" those places that first two fields are differ from each other with? (I tried formula REGEX_MATCH but it shows similarities between fields while I need differences).
Attached a sample how it should look like. Will be grateful for the help! 



1 Reply 1
Community Manager
Community Manager

Hi @Content_Remote_ 

You're on the right track with a REGEX() formula! You could create a REGEX formula here to make a field that says 'What in Field 1 does not exist in Field 2'. The function could look a bit like the following: 

REGEXREPLACE(REGEXREPLACE({Field 1}, "(" & REGEXREPLACE({Field 2}, ", ", "|" ) & ")(, )?", ''), "(, )$", "")

Admittedly, this solution isn't completely perfect for your use case. For example, it's a one-way formula. It will only look to see if Field 2 is missing values from Field 1, but not vice versa.

I'm sorry we don't have a formula in place that can more directly solve this issue. Comparing arrays or strings to find missing values is complex, and while I'm happy to add your vote in favor of this feature, please note it could take some time for our Formula team to find a solution that is compatible with our many field types.