Reconciliation of transactions from 2 separate systems

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-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)