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.
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.
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