Advanced Formula Help: Compare text from 2 lists

[emintzer]

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:
"email3"

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

[Abdullah_Yuksel]

Hi,

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.

[Lom_Labs]

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.

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!