Hi all,
Hope everyone is well and keeping safe.
I am trying to compare two data sets using a reference sheet. I am trying to create a workflow that takes the account # and account names from the reference sheet and find account# from data1 & account names from data2. The second part is to reconcile the amounts between data1 & data2 and show us the line items which are not matching between data1 and 2 Note: Dataset 2 has the amount broken into two or more line items and data1 has the consolidated amounts.
I will really appreciate any help here, thank you in advance.
Ref data:
| Accounts# | Account names | Account code |
| 1230012 | Expense | EXP-TT |
| 9817063 | Sales | SAL-TT |
| 1283263 | Ads | ADS-TT |
| 1987063 | Other | OTR-TT |
| 8881791 | Travel | TRL-TT |
| 4531628 | Pres and prod | PRE-TT |
| 7613960 | Telecom | Tel-TT |
Dataset 1:
| Business code | Accounts# | Currency | Amount |
| 1000 | 1230012 | EUR | 1100 |
| 2000 | 9817063 | USD | 2500 |
| 3000 | 1283263 | SEK | 100 |
| 4000 | 1987063 | USD | 3200 |
| 5000 | 8881791 | SAR | 45 |
| 6000 | 4531628 | EUR | 340 |
| 7000 | 7613960 | USD | 110 |
Dataset 2:
Business code | Currency | Amount | Account names |
1000 | EUR | 500 | Expense |
2000 | USD | 2200 | Sales |
3000 | SEK | 100 | Ads |
4000 | SAR | 200 | Other |
5000 | SAR | 45 | Travel |
6000 | EUR | 340 | Pres and prod |
7000 | USD | 90 | Telecom |
1000 | EUR | 500 | Expense |
2000 | USD | 600 | Sales |
4000 | SAR | 3000 | Other |
7000 | USD | 20 | Telecom |
Result:
Business code | Currency | Accounts | break amount with file name |
1000 | EUR | 1230012 | 100(more in dataset1) |
2000 | USD | 9817063 | 300(more in dateset2) |
