See also

Tags

spreadsheet solution
17 Jul 2024 09:27 PM

Joining Two Spreadsheets: AI or Workflows

close

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

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

clipboard-2024-07-17-21-20-00-607Z.png

  1. 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.
clipboard-2024-07-17-21-20-09-761Z.png

Apply a filter to the first dataset to select properties in Arizona.
clipboard-2024-07-17-21-20-15-251Z.png

Transform state codes in the first spreadsheet to full state names to match the second spreadsheet.
clipboard-2024-07-17-21-20-21-483Z.png

For each row in the first dataset, match and pull the corresponding interest rate from the second dataset.
clipboard-2024-07-17-21-20-28-002Z.png

Output the results directly into the new spreadsheet.
clipboard-2024-07-17-21-20-33-370Z.png