Description

The Reconcile function compares a source list of items (e.g. statement line items, invoice data) against a column in an Excel sheet to find rows that match. Matched rows are stored in a variable that you can loop through or inspect.

This is useful for accounts payable reconciliation, bank statement matching, invoice verification, and any scenario where you need to cross-reference two data sets.

How it works

Reads all rows from the specified Excel sheet
Applies any optional filter conditions (e.g. Vendor = Axiz) to narrow the rows
Extracts the match key from each source item using the Source key field
Compares each filtered Excel row's Match column value against the source keys
Rows that match are collected and stored in the specified variable

Setting it up

To use the EXCEL Reconcile function, follow these instructions:

Once on a process map:

Navigate to the process where you would like to add the EXCEL Reconcile function
Click on the green button's drop-down (right side of the green button labelled "Process")
On the menu that appears, click on the "+ Activity" item
From here you can select the EXCEL Reconcile option
Click "Add" once you are done

Input parameters

Parameter	Required	Type	Default	Description
Sheet	Yes	Text	-	The name of the Excel sheet to reconcile against
Match column	Yes	Text	-	The column in the Excel sheet that holds the reference/match key (e.g. Reference, Invoice No)
Source	Yes	Text	-	The variable containing the list of items to reconcile against (e.g. {statement.line_items})
Source key	Yes	Text	-	The field name in each source item to match on (e.g. reference)
Variable	Yes	Text	reconciled	Name of the variable where the matched rows will be stored as a list
Filters	No	Key/Operator/Value	-	Optional filters to narrow down which Excel rows to reconcile (e.g. Vendor = Axiz)
Remove whitespace	No	Switch	Off	Strip leading and trailing whitespace from values before comparing
Case-insensitive	No	Switch	Off	Ignore case when comparing match values

Output variables

Variable	Type	Description
{variable}	List	A list of matched Excel rows (as dictionaries). Each entry contains all columns from the matched row. Loop through with loop: "json" to process each match.

Example

Reconcile an AP Ledger sheet against bank statement line items, filtering for a specific vendor:

excel.reconcile(
    sheet: "AP Ledger"
    column: "Reference"
    source: "{statement.line_items}"
    key: "reference"
    filters: Vendor = Axiz
    variable: "matched_items"
    trim: true
    case: true
)

After execution, {matched_items} contains a list of AP Ledger rows whose Reference matched a statement line item reference. You can then loop through {matched_items} to process each match.

Errors

Error	Description
Sheet not found	The specified sheet does not exist in the workbook
Column not found	The match column does not exist in the sheet
Source is empty	The source variable is empty or not a list
Key not found	The source key field does not exist in the source items

EXCEL Reconcile function