Skip to main content

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.

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.

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

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

Reply


Terms & Conditions