Nov 12 2020 02:13 AM
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
Nov 12 2020 07:11 AM
i probably misunderstand "The main challenge".
But the easiest way to do what you want is probably to consolidate both workbooks into one pivottable.
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)