How can I compare two Excel sheets, each with around 200 rows, to identify discrepancies?
In my use case, the AI agent is not parsing all the data correctly and is returning either incomplete or incorrect output. How can I resolve this issue?"
Would you like to expand on your question, and provide any details? Like an example of two documents to compare, what is incorrect output, how you are using the AI Agent?..
Try to embed your workflow for us to take a look at.
the balances are not accurate and some vendors do not show in the list.
Ok, since you didn’t provide examples of xlsx files, I will assume the following format for this example:
Sheet 1:
| Account | Balance |
|---|---|
| acc1 | 10 |
| acc2 | 20 |
| acc3 | -5 |
| acc4 | -13 |
| acc5 | 7 |
Sheet 2:
| Account | Balance |
|---|---|
| acc1 | 10 |
| acc2 | 20 |
| acc3 | -6 |
| acc4 | -13 |
| acc5 | 17 |
This is the workflow I used:
Here is the output html I got:
How close is this to what you were looking for?
Thank you for your prompt response. When using the suggested workflow I got the following error “{
“nodes”: [
{
“parameters”: {
“promptType”: “define”,
“text”: “={{ $json.data }}”,
“options”: {
“systemMessage”: “You are tasked with reconciling liability accounts in Oracle EBS using two reports:\n\n1. Account Analysis Report\n2. Supplier Trial Balance Report\n\nInstructions:\n1. Match the Party Codes from the Account Analysis Report to the Supplier Codes from the Supplier Trial Balance Report.\n2. Compare the total balances for each vendor in both reports.\n3. Highlight any discrepancies, including:\n - Difference in balances.\n - Missing records (if a Party Code/Supplier Code exists in one report but not the other) \n4. Generate a detailed reconciliation report in HTML format with the following structure:\n - Vendor Name\n - Balance from Account Analysis\n - Balance from Supplier Trial Balance\n - Difference in Balance\n - Reason for Difference\n - Total Summary (including totals for matched, unmatched, and discrepancies).\n\nEnsure all records are processed, and the output is complete.”
}
},
“type”: “@n8n/n8n-nodes-langchain.agent”,
“typeVersion”: 1.9,
“position”: [
800,
300
],
“id”: “eff0240d-a701-4985-b72b-58c8d4a989d8”,
“name”: “AI Agent1”
}
],
“connections”: {
“AI Agent1”: {
“main”: [
]
}
},
“pinData”: {},
“meta”: {
“instanceId”: “daa14b75b58053eb4134d44f633ef7625fa7b804893a5ca8ad181a3c1e3c5b4d”
}
}”
I am uploading two sample reports that you can use for testing. Could you please help me resolve this issue?
Trail Balance:
| Vendor Name | Transaction Number | Balance |
|---|---|---|
| Vendor30 | SCF0672025 | 20,000.00 |
| Vendor30 | SCF0232025 | 8,000.00 |
| Vendor31 | SCF0442025 | 20,000.00 |
| Vendor2 | Mar-25 | 45,000.00 |
| Vendor2 | Feb-25 | 45,000.00 |
| Vendor3 | TI-2025-00002763 | 15,330.00 |
| Vendor3 | TI-2025-00002809 | 1,359.92 |
| Vendor3 | TI_2025-00003029 | 2,381.40 |
| Vendor3 | TI-2025-00004867 | 88.2 |
| Vendor3 | TI-2025-00004827 | 15,330.00 |
| Vendor3 | TI-2025-00004832 | 3,832.50 |
| Vendor3 | TI-2025-00004834 | 3,373.65 |
| Vendor32 | SCF0092025 | 12,000.00 |
| Vendor4 | VAIN25010761 | 36,500.00 |
| Vendor4 | VAIN25020210 | 17,850.00 |
| Vendor5 | SCF0762025 | 14,000.00 |
| Vendor6 | SCF0222025 | 6,000.00 |
| Vendor6 | SCF0302025 | 6,000.00 |
| Vendor6 | SCF0682025 | 15,000.00 |
| Vendor6 | SCF0722025 | 3,000.00 |
| Vendor7 | SCF0792025 | 19,000.00 |
| Vendor8 | SI-1001898 | 8,206.21 |
| Vendor8 | SI-1028859 | 6,880.88 |
| Vendor33 | 2.025E+11 | 10,000.00 |
| Vendor34 | 44658 | 945 |
| Vendor34 | 44583 | 3,832.50 |
| Vendor10 | 10006896560 | 5,140.80 |
| Vendor12 | 166122104 | 8,108.24 |
| Vendor12 | 167475200 | 7,681.53 |
| Vendor12 | 168834545 | 7,572.22 |
| Vendor13 | INV1953789970. | 840 |
| Vendor13 | INV1950446492. | 37,170.00 |
| Vendor13 | INV1950399235. | 31,901.10 |
| Vendor13 | INV1954235362. | 99.75 |
| Vendor13 | INV1953081140. | 798 |
| Vendor13 | INV1954823140. | 2,520.00 |
| Vendor13 | INV1959104457 | 840 |
| Vendor13 | INV1955380493 | 37,170.00 |
| Vendor13 | INV1955578959 | 29,885.10 |
| Vendor13 | INV195894950 | 99.75 |
| Vendor13 | INV1958020445 | 798 |
| Vendor13 | INV195208344 | 2,520.00 |
| Vendor35 | 226708 | 2,892.00 |
| Vendor35 | 228407 | 1,932.00 |
| Vendor36 | 222 | 15,000.00 |
| Vendor14 | FD/2025/0199. | -5,600.00 |
| Vendor37 | 192000800 | 525 |
| Vendor15 | 10066 | 3,780.00 |
| Vendor15 | 10068 | 5,733.00 |
| Vendor15 | 10082 | 2,940.00 |
| Vendor15 | 10069 | 640.5 |
| Vendor16 | HCE-SI-25020236 | 14,175.00 |
| Vendor38 | SCF0782025 | 15,000.00 |
| Vendor39 | 11255004420 | 9,735.60 |
| Vendor39 | 11255004421 | 23,000.00 |
| Vendor40 | 1063 | 5,381.25 |
| Vendor41 | SCF0392025 | 10,000.00 |
| Vendor41 | SCF0202025 | 4,000.00 |
| Vendor41 | SCF0432025 | 4,000.00 |
| Vendor41 | SCF0252024 | 8,000.00 |
| Vendor17 | Mar-25 | 45,000.00 |
| Vendor17 | SCF0362025 | 10,000.00 |
| Vendor42 | SCF0212025 | 4,000.00 |
| Vendor18 | INV-03093 | 74,189.00 |
| Vendor43 | MBRSOG-0125-352 | 23,887.50 |
| Vendor43 | MBRSOG-0225-345 | 23,887.50 |
| Vendor19 | IN-H012-023213 | 553.35 |
| Vendor19 | IN-H012-023516. | 553.35 |
| Vendor19 | IN-H031-009025. | 592.73 |
| Vendor19 | IN-H012-023701 | 553.35 |
| Vendor19 | IN-H012-023873 | 553.35 |
| Vendor19 | IN-H031-009150 | 592.73 |
| Vendor19 | IN-H012-024054 | 553.35 |
| Vendor19 | IN-H031-009277 | 592.73 |
| Vendor19 | IN-H012-024236 | 553.35 |
| Vendor19 | IN-H031-009488 | 592.73 |
| Vendor19 | IN-H012-024830 | 553.35 |
| Vendor20 | Mar-25 | 40,000.00 |
| Vendor20 | Feb-25 | 40,000.00 |
| Vendor44 | 200751 | 9,000.00 |
| Vendor45 | MVO-250012 | 9,371.25 |
| Vendor45 | INV-25030006 | 16,993.46 |
| Vendor21 | CRS/14471. | 1,890.00 |
| Vendor21 | CRS/14493. | 560.7 |
| Vendor21 | CRS/14505 | 2,541.00 |
| Vendor21 | CRS/14547 | 367.5 |
| Vendor21 | CRS/14567 | 9,925.82 |
| Vendor21 | CRS/14575 | 8,960.49 |
| Vendor21 | CRS/14606 | 1,197.00 |
| Vendor21 | CRS/14651 | 7,103.65 |
| Vendor21 | CRS/14859 | 840 |
| Vendor46 | SCF0612025 | 4,000.00 |
| Vendor47 | SCF0262025 | 10,000.00 |
| Vendor48 | 6567 | 5,001.75 |
| Vendor23 | INV-5124 | 3,990.00 |
| Vendor23 | 25/069800 | 2,015.00 |
| Vendor23 | NI4702770 | 4,137.00 |
| Vendor49 | 83339 | 229,911.68 |
| Vendor24 | SCF0752025 | 9,000.00 |
| Vendor50 | DXB-INV-2699 | 64,679.50 |
| Vendor50 | DXB-INV-2811 | 54,117.00 |
| Vendor51 | SCF0512025 | 4,000.00 |
| Vendor51 | SCF0522025 | 4,000.00 |
| Vendor52 | SCF0492025 | 30,000.00 |
| Vendor25 | SCF0772025 | 44,100.00 |
| Vendor26 | SCF0802025 | 33,000.00 |
| Vendor53 | SCF0562025 | 20,000.00 |
| Vendor54 | SCSG20240478. | 13,256.25 |
| Vendor27 | 202500174 | 2,080.00 |
| Vendor28 | CINV-078539 | 3,000.00 |
| Vendor28 | CINV-078540 | 3,000.00 |
| Vendor28 | CINV-078541 | 3,000.00 |
| Vendor28 | CINV-078685. | 2,100.00 |
| Vendor28 | CINV-078682 | 1,400.00 |
| Vendor28 | CINV-078683 | 1,400.00 |
| Vendor28 | CINV-078383 | 3,600.00 |
| Vendor28 | CINV-078046 | 2,400.00 |
| Vendor28 | CINV-078048 | 2,400.00 |
| Vendor28 | CINV-078384 | 3,237.15 |
| Vendor28 | CINV-078520 | 3,240.00 |
| Vendor28 | CINV-078521 | 1,200.00 |
| Vendor28 | CINV-078095 | 3,240.00 |
| Vendor28 | CINV-078094 | 3,240.00 |
| Vendor28 | CINV-079081 | 3,600.00 |
| Vendor28 | CINV-079084 | 3,600.00 |
| Vendor28 | CINV-078047 | 2,400.00 |
| Vendor28 | CINV-078542 | 3,750.00 |
| Vendor28 | CINV-078650 | 2,080.00 |
| Vendor28 | CINV-078651 | 2,080.00 |
| Vendor28 | CINV-078652 | 2,080.00 |
| Vendor28 | CINV-079541 | 4,405.80 |
| Vendor28 | CINV-078933 | 1,400.00 |
| Vendor28 | CINV-079401. | 3,600.00 |
| Vendor28 | CINV-079402 | 3,600.00 |
| Vendor28 | CINV-078932 | 1,400.00 |
| Vendor28 | CINV-079540 | 2,000.00 |
| Vendor55 | 13140048307 | 9,447.90 |
| Vendor55 | 13140048308 | 9,447.90 |
| Vendor29 | MB-00125 | 18,581.12 |
| Vendor56 | SCF0462025 | 12,000.00 |
Account Analysis
| Vendor Name | Transaction Number | Balance |
|---|---|---|
| Vendor2 | 1026 | 45000 |
| Vendor3 | TI-2025-00002763 | -15330 |
| Vendor3 | TI-2025-00002809 | -1359.92 |
| Vendor4 | VAIN25010761 | -36500 |
| Vendor5 | 1034 | 14000 |
| Vendor6 | SCF0722025 | -30000 |
| Vendor7 | 1028 | 19000 |
| Vendor8 | SI-962117 | -7758.36 |
| Vendor9 | SCF0062025. | -55095 |
| Vendor9 | 1033 | 55095 |
| Vendor9 | SCF0352025 | -55095 |
| Vendor10 | 10006775433 | -6027.84 |
| Vendor10 | 1004 | 6027.84 |
| Vendor11 | INV000350.. | -13440 |
| Vendor12 | 166122104 | -8108.24 |
| Vendor13 | INV1953789970. | -840 |
| Vendor13 | INV1950446492. | -37170 |
| Vendor13 | INV1950399235. | -31901.1 |
| Vendor13 | INV1954235362. | -99.75 |
| Vendor13 | INV1953081140. | -798 |
| Vendor13 | INV1954823140. | -2520 |
| Vendor13 | INV1950446492 | -35400 |
| Vendor13 | INV1954235362 | -99.75 |
| Vendor13 | INV1954823140 | 2520 |
| Vendor14 | 1136 | 2300 |
| Vendor15 | 10068 | -5460 |
| Vendor15 | 10068 | -273 |
| Vendor15 | 10066 | -3600 |
| Vendor15 | 10066 | -180 |
| Vendor16 | HCE-SI-25010446 | -14175 |
| Vendor17 | 1037 | 45000 |
| Vendor18 | INV-03093 | -74189 |
| Vendor19 | IN-H012-023213 | -1134.37 |
| Vendor19 | IN-H012-023213 | 581.02 |
| Vendor19 | IN-H012-023213. | -553.35 |
| Vendor20 | 1038 | 40000 |
| Vendor21 | CRS/14377. | -3609.9 |
| Vendor21 | CRS/14471. | -90 |
| Vendor21 | CRS/14471. | -1800 |
| Vendor21 | CRS/14493. | -582.7 |
| Vendor21 | CRS/14493. | 22 |
| Vendor22 | 1018 | 81938 |
| Vendor23 | INV-5124 | -3990 |
| Vendor23 | 904403 CASH | -3990 |
| Vendor23 | 904403 | 3990 |
| Vendor24 | 1035 | 9000 |
| Vendor25 | 1036 | 44100 |
| Vendor26 | 1039 | 33000 |
| Vendor27 | 202500174 | -1980.95 |
| Vendor27 | 202500174 | -99.05 |
| Vendor28 | CINV-078096 | 2000 |
| Vendor28 | CINV-078096 | 200 |
| Vendor28 | CINV-078685. | -2100 |
| Vendor28 | CINV-078685. | -105 |
| Vendor28 | CINV-078541 | 150 |
| Vendor28 | CINV-078541 | -3000 |
| Vendor28 | CINV-078682 | -1400 |
| Vendor28 | CINV-078683 | -1400 |
| Vendor28 | CINV-078383 | 180 |
| Vendor29 | MB-00125 | -18581.12 |
If it’s a simple dataset then you could maybe use the Compare Datasets node. You’ll need to have matching Transaction Numbers. Use the Transations Number as your comparible field name
Thank you for your reply. This node seems to retrieve all the data; however, I want to generate a unified report that highlights all the discrepancies using the AI agent.
Can you explain the logic for finding discrepancies? Show one example of each of
- having a discrepancy
- not having a discrepancy
in the data you shared for transaction and balance sheets above.