Re: Advanced Formula Help: Compare text from 2 lists

1582 0
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Goal: Create a list of email addresses that Accepted a Google Calendar Invite, a list of people that Declined, and a list of people that NeedAction.
My Data: I have 2 text fields with my data, called "All Emails" and "Attendee Status" (grabbed via Zapier).

All Emails = "email1,email2,email3,email4,email5, email6"
Attendee Status = "accepted, accepted, needAction, declined, accepted, declined"

In a new formula, I want to display everyone that accepted. It should print:
"email1, email2, email5"

In a new formula, I want to display everyone that declined. It should print:
"email4, email6"

In a new formula, I want to display everyone that needsAction. It should print:

Any help is appreciated; i feel like i exhausted the docs and attempts. Thanks!

2 Replies 2
6 - Interface Innovator
6 - Interface Innovator


I think you should consider changing the structure of the current table, which appears to be a list of events or similar items. I suggest creating a new table to link attendees and events, and including an attendee status column in this new table. This would make it easier to perform what you want using lookups.

7 - App Architect
7 - App Architect

Hello @emintzer , we've built an app that you may find helpful here.

The app will generate a script that you can add to your "Run a Script" automation action, and the script will  split the long text from multiple cells in the first table and create records in the second table, as well as linking the records together.

It does not matter how many data fields you have, so if you have the fields "IndividualAnchors1", "IndividualAnchors2", "IndividualAnchors3", "IndividualLink1", "IndividualLink2" for the same record, the app will still work fine.

Combine Fields and Create Records.gif

This will help you to create one record per email and status, and you can then use conditional lookups to display the emails based on their attendance status!