Forum Discussion
Reconciliation of transactions from 2 separate systems
Hi Community,
I have 2 spreadsheets from 2 various systems. Have a unique identifier. I am trying to match credit/debit balance towards the actual amount. The main challenge is some transactions need to total up to actual invoice amount. Any formulas I can use to match this records to get a outcome for actual amount owing?
TIA
1 Reply
i probably misunderstand "The main challenge".
But the easiest way to do what you want is probably to consolidate both workbooks into one pivottable.
https://support.microsoft.com/en-us/office/consolidate-multiple-worksheets-into-one-pivottable-3ae257d2-ca94-49ff-a481-e9fc8adeeeb5I
Otherwise you can try formulas. This will get all the UNIQUE identifier from
the open workbookA, ColumnA,Sheet1
I your new workbook. Put this in A1
=UNIQUE([WorkbookA]Sheet1!$A:$A)
To find the sums of values from colB, WorkbookA
Put this in B1
=SUMIF([WorkbookA]Sheet1!A:A;A1#;[WorkbookA]Sheet1!B:B)
and this in C1 (colB, WorkbookB)
=SUMIF([WorkbookB]Sheet1!A:A;A1#;[WorkbookB]Sheet1!B:B)
