Comparing between 2 Excel files

How To
1

Hi guys,

This is more of a theoretic question :nerd_face:: I have 2 Excel speadsheets. The Excels contain a trasaction report. The fields vary a bit between the reports, but share at least 2 common fields:

  • Refference Number
  • Amount

The 2 speardhseets are from the same time range (Date).

I would like to build a process that Gets both speadsheets, compares the Refference Number, and points out the differences. To do it, I was thinking about 2 options:

  1. Get the Refference Number from SHeet 1 , then Iterate and search them on Sheet 2. This option will consume a lot of operations
  2. Populate an Xlookup column on one of the Excels that will search for the refference number in the other sheet. This also seems to consume a lot of automations since I can populate one field at a time, since not being able to “drag” the formula to all other cells in the same column…

Any other ideas or options to save on operations? :thinking:

2

Unless I misunderstood your requirement, the “spreadsheet compare” tool in Excel ought to help. I am not sure why xlookup should consume operations if you are doing this in excel as formulae.

Another option could be to define the issue in ChatGPT if you are not familiar with VBA and get it to write it for you.

Good luck and let us know how you managed to tackle this issue.

3

I will explore “spreadsheet compare” and see what outputs it gives.

Xlookup will be populated field by field automatically via Make, so it will consume operations per field.

ChatGPT is probably out of the consideration due to privacy and cost.

Thank you for the direction ! :innocent:

4

Hello @OmriNyx,

Are these Excel 365 files or XLS files you’re dealing with?

Also, are you trying to compare only the amounts using reference number as a key? Or do you want to compare multiple fields?

5

I’m using Office 365.

I’m trying to compare the Reeference Number field. The amount field is usually (99.99%) matching once we associate the reffernce number from both sheets

What I’m trying to see is, for example, if Sheet 1 contains 102 trasactions and sheet 2 contains 98 trasactions, which are the trasactions that dont appear in either of the sheets, and their amount.

6

Do you mean which of the transactions don’t appear in the sheet with the least amount of transactions?

I would try this way:

  1. Use List Worksheet Rows on both spreadsheets.
  2. Use Array Aggregator to aggregate both into arrays. Now you have two arrays, each representing one of the sheets.
  3. Iterate one of the sheets, then follow with a JSON aggregator.
    The create a Data Structure for the JSON aggregator consisting of Reference Number, Amount 1, Amount 2
    Between the Iterator and JSON Aggregator, add a filter to only let bundles pass when the amount 1 doesn’t match amount 2 OR if Reference number exists in Array 1 but not Array 2.
    You can use map() and get() to pull the Amount from the Arrays based on Reference Number.
    You can add more filters for as many fields as you have.
  4. Parse JSON to get a bundle for each record where data didn’t match or didn’t exist in Array 2

Might look something like this:

image
image1488Ă—732 94 KB

2 Likes
7

wow :man_dancing: @Donald_Mitchell Sounds like a plan ! (and a good one !) :100:

Lets see if I can complete it, I’m considering this case as closed :blush:

3 Likes