Create a master list of unique account codes from two data sources

{ 2 Comments }

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.

Leave a Comment

Your email address will not be published.

*

  • In the scenario above, can you use powerpivot dax to
    1) matchup only the check numbers that are in both tables?
    2) show the check numbers that don’t match, i.e. show the checks that are “only” in the 2nd table, but are not in the 1st table.

    • Hi,

      So the answer that you want for your first question is a single column with the following cheque numbers – 123,456,567,789. For the second question, the result you want is a single column with the following cheque numbers – 89123,45091,5612. Is my understanding correct?

      This can be accomplished with Power Query. Do you want the result to be computed via Power Query or Power Pivot?