Joining Two Spreadsheets: AI or Workflows
Let's say you have two separate spreadsheets where the first spreadsheet contains a list of properties, and the second contains interest rates applicable in the regions of these properties.
Our objective is to create a third spreadsheet combining this information to display the property name, state code, and corresponding interest rate. Here are our example sheets:
Spreadsheet #1
| Property Name | State |
|---|---|
| ABC st.1 | AZ |
Spreadsheet #2
| State | Interest Rate |
|---|---|
| Arizona | 2% |
The AI-based Approach
- Verify that interest rates are searchable
1.1 Create a Chat
1.2 Connect Spreadsheet #2.
1.3 Verify the interest rate data by querying, for example, "Show Interest Rate for Arizona".
- Create a chat and run a new query to pull all columns and add an additional column for the interest rate corresponding to Arizona:
Show all columns. create an additional column with Interest Rate for Arizona.
In order to do that, find a row with State='Arizona'
Then, click the View button to see all columns and rows. Once you have the joined data, use the Copy button to transfer the data into your new spreadsheet.
Note:
We're using a very specific description 'In order to do that…' here because the 1st spreadsheet uses short codes, and the second spreadsheet uses full state names. This brings additional clarity for the AI
Alternative Approach (without using the AI)
This guide simplifies the process of linking data between two spreadsheets using direct queries and transformations without relying on AI-driven tools for every step.
Use the "Pull CSV" function for both spreadsheets.
Apply a filter to the first dataset to select properties in Arizona.
Transform state codes in the first spreadsheet to full state names to match the second spreadsheet.
For each row in the first dataset, match and pull the corresponding interest rate from the second dataset.
Output the results directly into the new spreadsheet.
