Assume a two column database in range C4:D11. In cell C3, type “Cheque No.” (without double quotes) and in cell D3, type “As per Bank Account” (without double quotes). Entries in range C4:C11 are cheque numbers and in range D4:D11 are Dollar values. Let’s refer to this database as “The Bank Dump”.
Assume another two column database in range I4:J9. In cell I3, type “Cheque No.” (without double quotes) and in cell D3, type “As per SAP” (without double quotes). Entries in range I4:I9 are cheque numbers and in range J4:J9 are Dollar values. Let’s refer to this database as “The SAP Dump”.
Please note that cheque numbers in range C4:C11 may or may not be present in range I4:I9. Likewise cheque numbers in range I4:I9 may or may not be present in range C4:C11
One may want to create a reconciliation statement i.e. a three column database showing “Cheque No.” in the first column, “As per Bank Account” in the second column and “As per SAP” in the third column.
Herein the first challenge is to create a master list of Account codes from two ranges, C4:C11 and I4:I9 and then the corresponding amounts from the two databases.
Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem
Solution for MS Excel 2010 and higher versions
If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem. The result will be dynamic and refreshable (just as in a Pivot Table).
You may refer to my solution in this workbook.
Solution for all versions of MS Excel
One may use using the Data > Consolidation feature. Please try the following steps:
1. Select cell C15 and go to Data > Consolidate
2. Under Function, choose SUM
3. Under reference, select range C3:D11 and the click on Add
4. Under reference, select range I3:J9 and the click on Add
5. Check the boxes for Top row and Left column
6. Click on OK
The result should be the three column database as desired above.
Create a master list of unique account codes from two data sources
{ 2 Comments }