In this tutorial, we will create an automation to compare the amounts in a spreadsheet that contains invoices with the amounts from a CSV file that contains a summary per supplier. If any reconciliation errors exist between the files, we will create a CSV file with details and send the file by email.

We will create a project with the following activities:

1. An Outlook Account Card and an Excel File Card to indicate the Outlook account we want to send the mail from and the Excel file with the invoices.
2. A second Excel File Card to indicate the file where we want to reconcile the data. Inside the card, we will add activities to copy the data from the two source files to this Excel file.
3. A series of Write Cell activities to add two column headers to the Excel file and to calculate the sum of invoices for one supplier and the difference between that sum and the amount in he initial summary. We will add a Copy Paste Range activity to copy the formulas we added for one supplier to all the rows in the sheet so that we can perform the same calculations for all the suppliers, and then add one more Write Cell activity to add the total difference to a cell.
4. An If activity where we will add a condition that if there are reconciliation errors (the sum of differences is greater than 0), a CSV file with details should be created and emailed to a specified address.

Step 1: Set up the project and get the necessary files.

1. Create a new project using the default settings.
2. Download and extract the archive with the automation project in this tutorial using the button at the bottom of this page. Copy the folder data to your project folder.

Step 2: Add the Excel file with the invoices and the Outlook account to the project.

1. Click on the Designer panel and then, in the Common Scenarios section of the search box at the top of the screen, select Use Outlook Account. An Outlook Account Card activity is added to the Designer panel.
2. In the activity, the default email account is already selected in the Select mail account field. If you want to use a different account, select it from the drop-down menu.
   In the And reference it later as field, leave the default value Outlook as the name under which to refer to the account in the automation.
3. Click on the Designer panel below the Outlook Account Card and then, in the Common Scenarios section of the search box at the top of the screen, select Use Excel File and browse to and select the file invoces.xlsx form the data folder. An Excel File Card activity is added to the Designer panel.
4. In the activity, in the field And reference it later as enter Invoices.
   You have indicated that you will work on the file invoices.xlsx that is known in your automation as Invoices.

Step 3: Add the Excel file where to perform the reconciliation and copy the data to it.

1. Click on the Designer panel below the Excel File Card and then, in the Common Scenarios section of the search box at the top of the screen, select Use Excel File and browse to and select the file reconcile.xlsx form the data folder. An Excel File Card activity is added to the Designer panel.
2. In the activity, in the field And reference it later as enter Rec.
   You have added the file reconcile.xlsx that is known in your automation as Rec to the automation project.
3. Click inside the second Excel File Card, and then find the Read CSV activity in the search box at the top of the screen and select it. The activity is added inside the Excel File Card.
4. In the Read CSV activity:
   • Click next to the Read from file field, and then browse to and select the file summary.csv.
   • Click on the right side of the Output to field, and then select Rec > Summary [Sheet]. The field is updated with your selection [Rec] Invoices.
     You have indicated that you want to copy the data from the CSV file summary.csv to the file Rec in the sheet Summary.
5. Click inside the second Excel File Card below the Read CSV activity, and then find the Copy Paste Range activity in the search box at the top of the screen and select it. A Copy Paste Range activity is added inside the Excel File Card.
6. In the Copy Paste Range activity:
   • Click on the right side of the Source range field, and then select Invoices > Invoices [Sheet]. Your selection is displayed in the field as [Invoices] Invoices.
     You have indicated that you want to copy the Invoices sheet from the file Invoices.
     Click on the right side of the Destination range field, and then select Rec > Invoices [Sheet]. Your selection is displayed in the field as [Rec] Invoices.
     You have indicated that you want to paste the copied range to the Invoices sheet in the file Rec.

Step 4: Make the necessary calculations to compare the data.

1. In the Activities panel, select the Excel tab, and then drag the Write Cell activity and drop it below the second Excel File Card. A Write Cell activity is added to the Designer panel.
2. In the Write Cell activity:
   • Click on the right side of the What to write field, and then select Text. In the Text Builder, enter the text Calculated Amount.
   • Click on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the cell C1, and then click Confirm. The field is updated with your selection [Rec] Summary!C1.
     You have indicated that you want the first cell in column C from the Summary sheet of the Rec file to contain the text Calculated Amount.
3. Add a second Write cell activity below the previous one and, in the activity::
   • Click on the right side of the What to write field, and then select Text. In the Text Builder, enter the text =IF(A2 <> "", SUMIF(Invoices!B:B, A2, Invoices!C:C), "").
   • Click on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the cell C2, and then click Confirm. The field is updated with your selection [Rec] Summary!C2.
     You have indicated that you want C2 from the Summary sheet of the Rec file to contain the formula =IF(A2 <> "", SUMIF(Invoices!B:B, A2, Invoices!C:C), ""). This formula checks if the supplier cell (A2) is not empty and sums the values in the C column of the Invoices sheet for the supplier in the cell A2 of the Summary sheet.
4. Add a third Write cell activity below the previous one and, in the activity::
   • Click on the right side of the What to write field, and then select Text. In the Text Builder, enter the text Difference.
   • Click on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the cell D1, and then click Confirm. The field is updated with your selection [Rec] Summary!D1.
     You have indicated that you want the first cell in column D from the Summary sheet of the Rec file to contain the text Difference.
5. Add a fourth Write cell activity below the previous one and, in the activity::
   • Click on the right side of the What to write field, and then select Text. In the Text Builder, enter the text =IF(A2 <> "", C2-B2, "").
   • Click on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the cell D2, and then click Confirm. The field is updated with your selection [Rec] Summary!D2.
     You have indicated that you want D2 from the Summary sheet of the Rec file to contain the formula =IF(A2 <> "", C2-B2, ""). This formula checks if the supplier cell (A2) is not empty and calculates the difference between the values in cells C2 and B2 of the Summary sheet.
6. Click below the last Write Cell activity, and then find the Copy Paste Range activity in the search box at the top of the screen and select it. A Copy Paste Range activity is added to the Designer panel.
7. In the Copy Paste Range activity:
   • Click on the right side of the Source range field, and then select Invoices > Indicate in Excel. The spreadsheet is opened in Excel. Select the range C2:D2, and then click Confirm. The field is updated with your selection [Rec] Summary!C2:D2.
   • Click on the right side of the Destination range field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the range C3-D20, and then click Confirm. The field is updated with your selection [Rec] Summary!C3-D20.
   • From the What to copy drop-down menu, select Formulas
     You have indicated that you want to paste the formula in the copied range C2:D2 down to the same columns in the sheet.
8. In the Activities panel, select the Excel tab, and then drag the Write Cell activity and drop it below the Copy Paste Range activity. A Write Cell activity is added to the Designer panel.
9. In the Write Cell activity:
   • Click on the right side of the What to write field, and then select Text. In the Text Builder, enter the formula =SUM(Summary!D:D).
   • Click on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the sheet Invoices, then the cell F1, and then click Confirm. The field is updated with your selection [Rec] Invoices!F1.
     You have indicated that you want the first cell in column F from the Invoices sheet of the Rec file to contain the formula =SUM(Summary!D:D). The formula adds the sum of the differences in the D column of the Summary sheet to the F1 cell in the Invoices sheet.

Step 5: Send an email with details about the reconciliation if the sum of differences is greater than 0.

1. Click below the last Write Cell activity and then, in the search box at the top of the screen, locate and select If. An If activity is added to the Designer panel.
2. In the If activity, click on the right side of the Condition field, and then select Condition Builder.
3. In the Condition Builder, add the condition "if the sum of differences is greater than 0":
   • Click on the right side of the field on the left, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the sheet Invoices, then the cell F1, and then click Confirm. The field is updated with your selection [Rec] Invoices!F1.
   • From the drop-down menu in the middle, select greater than.
   • Click on the right side of the field on the right, select Number, enter 0 and click Save twice.
     The activities you add in the Then branch of the If activity will be executed if this condition is met.
4. In the Activities panel, select the CSV tab, and then drag the Write CSV activity and drop it in the Then branch of the If activity. A Write CSV activity is added to the Designer panel.
5. In the Write CSV activity:
   • Click on the right side of the Write to what file field, and then select Text. In the Text Builder, enter reconcile , and then from the menu on the right side of the Text Builder select Workspace Excel> Date [Sheet] > YYYYMMDD [Cell]. The text in the Text Builder is updated to reconcile Excel Date!YYYYMMDD. Enter the text .csv at the end and click Save. The final text should be reconcile Excel Date!YYYYMMDD.csv.
   • Click on the right side of the Write from field, and then select Rec > Summary [Sheet]. The field is updated with your selection [Rec] Summary.
     You have indicated that you want to create a CSV file in the project folder whose name contains the word reconcile and today's date and copy the Summary sheet of the Rec file to it.
6. In the Activities panel, select the Outlook tab, and then drag the Send Outlook Email activity and drop it in the Then branch of the If activity below the Write CSV activity.
7. In the Send Outlook Email activity:
   • Click on the right side of the From account field, and then select Outlook.
   • Click on the right side of the To field, and then select Text. In the Text Builder window, enter an email address where to send the email. For example, you can enter your own email address to send the email to yourself.
   • Click on the right side of the Subject field, and then select Text. In the Text Builder window, enter Reconciliation errors for, and then from the menu on the right side of the Text Builder select Workspace Excel> Date [Sheet] > Today [Cell]. The text in the Text Builder is updated to Reconciliation errors for Date!Today.
   • Click on the right side of the Body field, and then select Text. In the Text Builder window, enter text for the body of the email, for example One or more suppliers have reconciliation errors. See attached CSV file for details..
   • For Attachments, select Files, then click next to the field, and then browse to and select the file B.xlsx.

5. Click Save in the ribbon menu to save the automation, then click Run to execute the automation.

The invoices and summary data are copied to the reconcile.xlsx file, the calculations are performed and, because the total amount in the invoices does not match the one in the summary for one of the suppliers, the Summary sheet of the reconcile.xsls file is copied to a CSV file whose name includes today's date, the file is attached to an email and sent to the specified address. In the file, you can identify the supplier with reconciliation errors by the value in cell D3 that is greater than 0.

Download example