Compare results in two tables

[Patrick_Hunt]

Hello,

I’ve created a base by importing two separate CSV files as tables. Each table contains a list of email addresses. I’d like to easily be able to find email address that are in both tables. How do I go about that? Thanks! (new to Airtable)

[Jonathan_Fuller]

Create a third table with a formula that performs a comparison of the two tables/fields and outputs either the email address when they appear in both or a variable such as ‘yes’ to indicate that they appear in both.

You will still need a mechanism to trigger the creation of records on the third table. I’d use Zapier to Add a Record on View Change. Set the View to Main on the second table and when you import the records on both tables it will trigger the creation of a record on the third table that performs the necessary comparison.

[Thao_Tu1]

Hello,

Would you mind walking me through or linking me to where I can find this process in detail? Step by step if possible.

Thank you!

[W_Vann_Hall]

Here’s what I think is an easier method:

1. Import your first table.

2. Link every record of that table to a single record in a second table.

3. Import your second table.

4. Link every record of that table to the same single record to which you linked in Step 2.

5. In the table with a single record, define a rollup field that 1) follows the link back to the first table and 2) rolls up {Email Address} using an aggregation function of ARRAYJOIN(values). Call this field {Emails}.

6. In table 2, create a rollup field called {IsDupe} that 1) follows the link to the [Calc] table, 2) rolls up {Emails}, and uses this aggregation formula:

   FIND({Email Address},values)>0

Any record in table 2 where {IsDupe} = 1 contains an email address that duplicates an address present in table 1.

[Matteo_Cossu2]

I don’t know if it’s related to an update in airtable since this post or if it’s just my table, but I’m finding i need to convert the result of the rollup to a string otherwise the FIND function won’t work.

FIND({Email Address},CONCATENATE(ARRAYUNIQUE(values),""))

[steffictmk]

I've been looking for a way to do this for several hours !!
Thank you so much 🙏 @W_Vann_Hall W_Vann_Hall  &  @Matteo_Cossu2