Skip to main content

Compare 5 Tables to Find a Matching Field

  • March 10, 2022
  • 3 replies
  • 64 views
W

I have data for 4 competitors and myself , formatted exactly (5 tables).
Each set of data contains a column of keywords. Some competitors have some KWs that match what I rank for. What would be a good way to set up a 6th table or add columns to my table, to indicate a competitor has a matching keyword. Normally I would do a vlookup in Excel, but I think this would be a lot more powerful in Airtable.

Example of Report Needed (I can hide columns if needed):

My Keywords Competitor1 Competitor2
Apples Match No Match
Oranges Match Match
Grapes No Match No Match

I tried linking to each competitors record and copying my KWs in column, and it will add the matched KWs to the competitor tables, but it also adds all the non-matches at the bottom.

Example

My Keywords Link to Competitor1 Link to Competitor2
Apples Apples Apples
Oranges Oranges Oranges
Grapes Grapes Grapes

Any help would be greatly appreciated.

This topic has been closed for replies.

3 replies

Kamille_Parks11
Forum|alt.badge.img+27

Question: why are you using several Tables instead of a single Table with multiple filtered Views?

If you’re adamant about keeping them as separate tables, every Keyword column you have should be converted to a Link to Record field pointing to a table of Keywords. That will show you each unique keyword as its own record and you’ll see which competitors are linked to each keyword.

W
  • Waterguy
  • Author
  • New Participant
  • March 11, 2022

Question: why are you using several Tables instead of a single Table with multiple filtered Views?

If you’re adamant about keeping them as separate tables, every Keyword column you have should be converted to a Link to Record field pointing to a table of Keywords. That will show you each unique keyword as its own record and you’ll see which competitors are linked to each keyword.


I could create a single table, but then I would have duplicate Keyword values in the same column.
So I could then filter on my and a competitor’s set. But how would I filter on the duplicate keywords?
I’m talking 1000s of KW per company with a hundred matches.

Company | Keyword
Me | Apple
Me | Orange
Me | Grape
Competitor 1 | Grape
Competitor 1 | Mango
Competitor 1 | Pear

Kamille_Parks11
Forum|alt.badge.img+27

I could create a single table, but then I would have duplicate Keyword values in the same column.
So I could then filter on my and a competitor’s set. But how would I filter on the duplicate keywords?
I’m talking 1000s of KW per company with a hundred matches.

Company | Keyword
Me | Apple
Me | Orange
Me | Grape
Competitor 1 | Grape
Competitor 1 | Mango
Competitor 1 | Pear


Again, the Keyword column would link to the Keywords table. In the Keywords table you could have a count field that shows how many companies are linked to it, and then set a filter for when Count > 1

Terms & ConditionsAccessibility statement