Formula to find unique text values between two rollup columns?

#1

I have two rollup columns that each contain (potentially a lot) of text values. Rollup column 1 will most likely contain some, but not all, of the same text values of Rollup column 2.

I need a way to write a formula for a third column that pulls out any unique text that exists in column 2 that does not exist in column 1 and then shows what those unique text values are in column 3.

(Essentially I’m trying to find values that don’t match between the two columns, but I don’t want to calculate just a true or false statement as to whether the two columns match. I want to know what the text values are.)

Anyone that can help?

-Thanks

#2

Welcome to the community, Tadd! :smiley: My brain initially went one way when you mentioned rollup columns (I use them frequently for cross-table calculations), but after creating a solution based on that assumption, I came back here to write about it and realized that you might be talking about something completely different.

Can you offer an example of the kind of source data you have in your two rollup columns (fields)? Are we talking about lots of records, and you want these differences tracked on a record-by-record basis, or (as I initially assumed) a single record that rolls up data into two fields, and you just want to find the differences between the two rollup fields in that single record?

Here’s the thing. Rollups create arrays. While Airtable does have some functions that operate on arrays, there isn’t yet one that will find the difference between two arrays. The solution I came up with basically works around that, but it’s designed to operate on a rollup that’s the aggregation of a ton of values from another table into a single cell of a single record. It won’t work on a rollup that changes on a record-by-record basis. To my knowledge, that kind of array difference calculation isn’t currently possible, even with clever table gymnastics.

#3

Hi Justin, thanks for the response!

So I’ll try to explain this the best I can. I’m using my database for business purposes within a manufacturing plant. More specifically, I want to track training certifications for employees.

The way this process works in our plant is that there are many individual processes that an employee must be certified to. But there are specific roles that require an employee to possess a specific “set” of those individual certifications to be considered certified for the role.

The data that is in the two rollup columns contain controlled document numbers for individual training documents (in other words, an individual process). Each controlled document number is a single “string” (if that’s the proper terminology?) without any spaces or anything, but it is comprised of both letters and numbers.

An example of one of the controlled document numbers would be : " SOP-101-6002_OPERATION ".

The first column will be a rollup of a bunch of controlled documents that an individual employee has been certified to.

The second column will be a rollup of a bunch of controlled documents that are required for an employee to be certified for a specific job role.

The third column would then look to the first column to see if any of the controlled document numbers that the employee has already been certified to match the ones “required” in the second column for being certified to a specific role. I would like the third column to list any controlled documents that are unique to the second column to be listed. (Essentially showing which individual training certifications the employee still needs to be certified for the role.) If the employee possesses all the training certifications (column one) that are required (column two) then I would like it to return a green checkmark emoji to illustrate that the employee possesses the documents required and is therefore “certified” for the role.)

Employee names would be the “records” (or rows). The employee’s individual controlled documents they’ve been certified to would be rollup column one and the job role with its own group of required controlled documents would be column two.

The problem I’m having is that column one will be representing every individual controlled document that the employee may have, including ones that don’t apply to the role they’re attempting to be certified to. I need it to ignore any additional certifications the employee may possess (in column one) and only look for the applicable ones that match the ones required for the role (in column two).

My trouble might be that I don’t fully understand the characteristics (or rules) of how “strings” and/or “arrays” work. My database is pretty robust and I’m pretty proud of some of the clever tricks I’ve been able to implement so far (or table gymnastics as you cleverly put it :smile:). But when it comes to working with manipulating text my formula knowledge is admittedly a little more lacking. I’ve been able to use simple functions and such to manipulate text, but maybe what I’m trying to make Airtable do is too ambitious? Maybe I need a proper SQL system?

(Sorry for the novel! I hope that made sense? :sweat_smile:)

#4

Thanks for the follow-up, Tadd. That makes complete sense (and trust me, novels are far better than short stories when it comes to explaining what you’re trying to achieve with a database). Your assumption that your controlled document numbers are strings is correct. Arrays are simply collections of items. They could be collections of strings, numbers, dates, etc. In your case, your rollup arrays are collections of controlled document number strings.

Directly within Airtable, it’s not currently possible to compare arrays to find their differences, at least not on a record-by-record basis as you need (I may post my other solution to the Show and Tell category in case it helps elsewhere, but sadly it won’t help here). That would require either a function that specifically finds the differences between two arrays—perhaps named something like ARRAYDIFF—or the ability to iterate through an array and perform some operation/comparison on each item. Sadly array iteration isn’t currently possible in Airtable, and ARRAYDIFF only exists as a dream in this thread (and soon the Product Suggestions category).

However, it might be possible to achieve what you want through integration with Zapier, Integromat, or another similar service. I know very little about either of those, so I’ll let others chime in re: what can be done on those fronts.

#5

Thanks for confirming that my thought process was at least headed in the right direction. Comparing arrays with an ARRAYDIFF function seems like it should almost be an assumed option for users. I’m sure others would find good use for it. Hopefully we’ll see this added soon. The folks at Airtable seem to continue to support the platform pretty well from the short time I’ve been using it. (I’ve been excitedly updating my tables with the recently released LAST_MODIFIED_TIME function.)

Here’s to hoping we see an ARRAYDIFF function in the future!

Yes, I have experience using Zapier in the past, but it didn’t even occur to me to use it! I might be able to use Zapier to route it through Google Sheets and then get Google Sheets to do some manipulation before sending it back through Zapier to Airtable. :thinking:

Thanks again Justin!