To address your original request — namely, to compare two arrays and remove all elements found in both — it is likely doable, albeit not very pretty. One limiting factor is Airtable’s current inability to loop through processes. Instead, one has to create an explicit loop for up through the expected maximum number of iterations. The second limiting factor is the potential ungainliness required to address variable-length array items. (For an example of how challenging this can be, take a look at my reply to a user who wished to extract hashtags from text.)
[Brief break to go read your feature request.]
Oh, right — I remember that! Not sure why I didn’t respond; possibly because @Jeremy_Oglesby beat me to it. I also hadn’t wrapped my head around that hashtag request noted earlier, so I hadn’t dealt with the issue of how to address the nth anything from free-form text.
That said, I’m not going to address that, here — check the earlier link to see why. Instead, I’ll offer some pseudocode for returning an ArrayC containing only those items in ArrayB that aren’t present in ArrayA, assuming array items of a standard, fixed length.
By that last bit I mean each item in the array is the same number of characters long. Depending on the nature of the items, this could be accomplished either by modifying the items as exposed to the user — for instance, by changing 'Rain jacket: W XS' to 'RJkt:W:SS' — or behind the scenes, performing a substitution before and after XORing the array. For sake of your UX, I’ll assume the latter.
Assumptions: I’m not sure how you’re creating your arrays. (If you are rolling up linked records and having trouble getting ARRAYUNIQUE() to work as expected, see this reply.) I assume, though, your arrays look something like this:
Rain jacket: W XS, Rain jacket: M XS, Sleeping bag: Single
- Normalize both
{Gear Requested} and {Gear Promised} to fixed-length fields, This is done with nested SUBSTITUTE() functions, one depth per possible item:
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
{Gear Requested,
'Rain jacket: W XS',
'RJWXS'
),
'Rain jacket: M S',
'RJMS '
),
'Sleeping bag: Single',
'SBS '
),
'Sleeping bag: Double',
'SBD '
)&','
Do the same for {Gear Promised}. This should result in encoded strings in this format (with a trailing comma):
RJWXS, RJMS , SBS ,
Note that codes with fewer than five digits are right-padded with spaces. Also note that, for deeply nested SUBSTITUTE()s, I often find it easier not to indent as I would an IF() statement but instead concatenate the function calls, as so:
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
{Gear Requested,`
'Rain jacket: W XS',
'RJWXS'
),
'Rain jacket: M S',
'RJMS '
),
'Sleeping bag: Single',
'SBS '
),
'Sleeping bag: Double',
'SBD '
)
- Figure out how many elements are in
{Gear Promised encoded}. Keep in mind this is not the number of elements you can subtract from {Gear Requested encoded}; that number will be determined by a hard maximum built into the formula. Instead, this will be a number less than or equal that maximum indicating how many iterations of the subtraction process to apply. Calculating this upfront greatly simplifies the resulting formula, as it no longer has to determine whether each iteration is valid. I’m going to call the field {NumberOfItems}; it’s a formula field with the following formula:
LEN(
{Gear Promised encoded}
)-LEN(
SUBSTITUTE(
{Gear Promised encoded},
',',
''
)
You may want to throw an alert if {NumberOfItems} is greater than {MaxItems}.
- For up through
{MaxItems}, eliminate items from {Gear Requested encoded} that match an item in {Gear Promised encoded}. The following assumes {MaxItems} of 5; it can easily be expanded.
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
{Gear Requested encoded},
IF(
{NumberOfItems}>=1,
MID({Gear Promised encoded},1,5),
''
),
''
),
IF(
{NumberOfItems}>=2,
MID({Gear Promised encoded},8,5),
''
),
''
),
IF(
{NumberOfItems}>=3,
MID({Gear Promised encoded},15,5),
''
),
''
),
IF(
{NumberOfItems}>=4,
MID({Gear Promised encoded},22,5),
''
),
''
),
IF(
{NumberOfItems}>=5,
MID({Gear Promised encoded},29,5),
''
),
''
)
-
This leaves you with {Gear Denials encoded}. Reverse the process of Step 1 to return the array to user-friendly phrasing. Note: The resulting array will have a dangling comma; if that bothers you the way it would me, wrap {Gear Denials encoded} with
LEFT({Gear Denials encoded},LEN({Gear Denials encoded})-1)
before decoding to {Gear Denials}.
If you want an array containing all items unique to {Gear Requested} or {Gear Promised}, process once for {Gear Requested} - {Gear Promised} and once for {Gear Promised} - {Gear Requested} and then combine the arrays.
