Reconciliation of transactions from 2 separate systems

Copper Contributor

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

@Sagar7874 

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-3ae25... 

 

 

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)