Excel: Join two sheets

Use this action to combine two Excel worksheets by matching a common column and then adding columns. Similar to VLOOKUP. This action supports .XLSX, .XLS, .XLSM, and .CSV files.

screen readers look here

This action only joins data by adding new columns of data, you cannot replace data with this action. To add new rows of data, like combining two similar spreadsheets, use the Excel: Combine spreadsheets.

How to configure this action

This action compares and joins two spreadsheets based on key columns, which is the column each spreadsheet shares. If there is a row present in both the master and inbound spreadsheets, column data from the inbound spreadsheet is added to the master spreadsheet.

Here’s an example of how to configure this action; we will join two spreadsheets to add a SKU number to a list of fruit based on the different fruit varieties:

  1. Set both key columns so there is matching data.
  2. In the example below, the key column is “Fruit”
  3. Between the two Fruit columns, there are two shared rows: Apple and Banana.

    screen readers look here
  4. Now that the key columns are set, choose the columns to add to the master spreadsheet. We want to add the “SKU” column.
  5. This joins the SKU column onto the master spreadsheet.
  6. Only the data from matching rows is added; because “Lime” is not on the master spreadsheet, it is not transferred from the inbound sheet.

    screen readers look here

💡   Tip: The best columns to use as key columns are those with unique data, like usernames or IDs, not overlapping data like age or city.

Fields for this action

  • Master spreadsheet file

    • This is the spreadsheet you want to join data to. Use a field reference to a file uploaded in a prior task or instance.
      • .xlsx, .xls, .xlsm, and .csv are supported.
  • Master sheet

    • The worksheet within the spreadsheet that contains the data. Enter the sheet name or the sheet order.
      • The default Excel sheet names are “Sheet1”, “Sheet2”, etc.
      • Enter a number to select by worksheet order, “1”, “2”, “3”, etc.
  • Master sheet header row number

    • Enter the row number that contains the names of the columns. Leave blank if the worksheet does not have a header row.
    • ✅   Heads-up: If blank, columns can only be referenced by Excel or column order reference style.
  • Master sheet column reference type

    • Enter the column reference type to use during configuration; Column Name, Excel, or Column Order. Subsequent fields must be configured according to the column reference type you choose.
    Column name If a header row is set, you can reference columns directly by their name in {{column-name}} format.
    Excel You can reference columns by their Excel letter, such as A, B, C.
    Column order You can reference columns by their order, such as 1, 2, 3.
    • If Header row number is not specified, only Excel or Column Order references can be used.

    • ✅   Heads-up: If using column name reference type, enclose a column name in quotation marks if it contains special characters such as commas, leading and/or trailing whitespace, and newlines. If the value contains quotation marks, they must be escaped with a quotation mark.

  • Master key column

    • Choose the column to look up by. Select a column shared between both files.
    • Reference the column based on the column reference type specified above.
  • Inbound spreadsheet file

    • This is the spreadsheet you want to join onto the master spreadsheet. Use a field reference to a file uploaded in a prior task or instance.
      • .xlsx, .xls, .xlsm, and .csv are supported.
  • Inbound sheet

    • The worksheet within the spreadsheet that contains the data. Enter the sheet name or the sheet order.
      • The default Excel sheet names are “Sheet1”, “Sheet2”, etc.
      • Enter a number to select by worksheet order, “1”, “2”, “3”, etc.
  • Inbound sheet header row number

    • Enter the row number that contains the names of the columns. Leave blank if the worksheet does not have a header row.
    • ✅   Heads-up: If blank, columns can only be referenced by Excel or column order reference style.
  • Inbound column reference type

    • Enter the column reference type to use during configuration; Column Name, Excel, or Column Order. Subsequent fields must be configured according to the column reference type you choose.
    Column name If a header row is set, you can reference columns directly by their name in {{column-name}} format.
    Excel You can reference columns by their Excel letter, such as A, B, C.
    Column order You can reference columns by their order, such as 1, 2, 3.
    • If Header row number is not specified, only Excel or Column Order references can be used.

    • ✅   Heads-up: If using column name reference type, enclose a column name in quotation marks if it contains special characters such as commas, leading and/or trailing whitespace, and newlines. If the value contains quotation marks, they must be escaped with a quotation mark.

  • Inbound key column

    • Choose the column to look up by. Select a column shared between both files.
    • Reference the column based on the column reference type specified above.
  • Columns to return to master sheet

    • A list of columns, separated by commas, to return from the inbound sheet and add to the master sheet.
    • Reference the column based on the column reference type specified above.

      • If left blank, all columns in the inbound sheet will be added to the master sheet.
      • If using column name as the inbound sheet column reference type enclose a column name in quotation marks if it contains special characters such as commas, leading and/or trailing whitespace, and newlines. If the value contains quotation marks, they must be escaped with a quotation mark.
  • Output field name

    • To help keep output fields organized, choose a prefix that will be added to the beginning of the output field name. The name you specify will become the output field prefix for the field.

What will this output?

This action may generate multiple fields. To help keep output fields organized, the prefix above will be added to the beginning of each of the output field names, separated by two dashes. Each field will result as:{{output-field-prefix--output-field}}. Learn more

Output fields for this action

  • Filtered Spreadsheet

    • The field where the filtered spreadsheet file is stored
  • Sheet Name

    • The sheet name in the new spreadsheet where the filtered information is stored.

Tips

  • Use an Instance Field to upload your Excel file so that it is available to every step of the Workflow. Doing so will also ensure that a file is always uploaded before your instance begins.

Sorry about that. What was the most unhelpful part?









Thanks for your feedback

We update the Help Center daily, so expect changes soon.

Link Copied

Paste this URL anywhere to link straight to the section.

Need more help?

If you're signed in to Catalytic Community, you can ask other users a question. You'll be redirected to Community where you can add more info.