Combining Tables into one table

[zhenson]

I have two tables as follows:

Matters table:

Matter(s)

Description

Client Name

Lead Source

Bills table:

Client Name

Matter(s)

Total ($)

Both of these tables are pulling from two different data sources. I need a way to combine them both into one table so that table 3 is as follows:

Lead Source

# Matter(s)

Total ($)

I believe the best way to do this is to create records, but I'm not sure. In addition to combining the data from two tables into one, I also need the data to stay consistent (i.e. Client Name on matters table and bills table combine into one data set on 3rd table so that Lead Source and Matters can be "associated" with them.)

If anyone knows of a way to approach this, I would appreciate the help. I've been working on it for over a week.

[pressGO_design]

When you say "Both of these tables are pulling from two different data sources" do you mean that they are syncing from other Airtable bases or they are coming into Airtable from outside sources via Zapier/Make/other integration?

[zhenson]

@pressGO_design Thank you for the reply. Currently, I am manually exporting this data from an excel spreadsheet. Automating it will come later. Just trying to get the basic functionality down first.

[pressGO_design · Accepted solution]

Best. News. Ever. Since you’re manually pasting stuff in, you can play around with linking fields without messing up existing syncs and integrations. 

OK. Step 1 is make a copy of your base. In the copied base, you’re going to create a table called Lead Sources and you’re going to put all your Lead Sources in the Name column of that table. You’re probably going to need to dedupe them, but since you’re already using Excel, you can probably do that there. Once you’ve done that, you link the Lead Source field in your Matters table with your Lead Source table. That will allow you to get the rollup you need for the #Matter(s) field. 

Then, in your Bills table, you’re going to link the Matter(s) field to the Matters table. Important note: if you have duplicate Matter Names in your Bills table that do not refer to the same Matter, do not do this! Instead, create a data structure that uses a formula to create Client-Matter IDs (or do something else to ensure that your Matters are unique).

Once you’ve linked your Bills and Matters table via the Matter(s) field, you can use a rollup in the Matters table to calculate the Total for each matter and then use a rollup in your Lead Source table to get the total dollar amount for all the Matters per Lead Source.

[zhenson]

Thank you very much! This worked perfectly. Your explanation was very easy to understand and accomplished exactly what I needed it to do.