Mar 10, 2022 02:42 PM
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.
Mar 10, 2022 03:17 PM
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.
Mar 11, 2022 04:17 AM
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
Mar 11, 2022 09:13 AM
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