Compare comma-separated emails across two cells and identify the ones without a match?

Hey all,

I’ve looking through a few others threads and haven’t found a solution so I’m hoping the hive mind can help: a bit of a tricky scenario, but I’m essentially looking for a way to compare comma-separated emails across two cells and identify the ones without a match.

Scenario: I have an airtable of events, where each event has:

  • a column called “invitees” (people invited to an event), where multiple email addresses are separated by commas within a cell – the values are a string
  • a column/cell of emails called “RSVP’s” (people who have already replied ‘going’), where multiple emails are separated by commas as well – the values are a array that rolls up data from another table.

So we’ve got two comma separated cells, one is a string and one is an array.

I would like to send reminder emails only to the people on the invitee list who did not already RSVP (aka, remove RSVP emails from the invitees list, then email only the ones left over, aka the ones without duplicates). Is there a way of doing this without creating new columns for each email address?

Thanks for any help you can provide!

Brainstorming solutions
I thought I could turn them into arrays and somehow compare them, but I doubt there’s a way to do a compounding if statement for each item in the array, similar to a for loop in php (e.g., if [columnA arrayvalueA] is in columnB, don’t list it, else list it; if [columnA arrayvalueB is in column B, …]. It seems beyond airtable’s formula capabilities.

This should be relatively easy to solve.

Just create a brand new table where the primary field is email address.

Then, back in your original table, convert each of your email fields to a linked record field, and point both linked record fields to the new table.

Now, in your brand new table, you will be able to see which email addresses have a particular event listed in the “invitee” column but not the “RSVP’d” column, and you could create a filter for that as well.

Hmm I think that works if it’s just one event, but each row is a different event, so if I created a new record in airtable, it would count invites and rsvps across events and those would be greater than 1 if invited to multiple events. And if someone rsvp’s but was invited via a link instead of an invite on the platform, the count of rsvp’s > invites.

Yes, it would work with different events. In your new table, each email address would show you a linked record field displaying all the events that the person was invited to, and another linked record field displaying all the events that the person RSVP’d to.

This is called a one-to-many relationship. However, if you need even more granular tracking of events than this, then you will need to change the structure of your database to become a many-to-many relationship. Here’s Airtable’s guide on many-to-many relationships:

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.