help with matching formaula or pivot table for reconciliation with no unique ID please?

[nimv1]

Hi there,

I hope you can help by helping with a formula or pivot table in how to do a bank reconciliation?
The trouble is there is no unique ID, only by aligning the amounts with the particulars (deleted from example) will I know if they can be reconciled.
Therefore, what I would like to do from the attached document is align transactions from our GL to the bank statement.
The top half shows the our GL (upto about row 267) and the bottom half shows transactions from the bank statement. Debits on the left (column C) and credits on the right (column G).
Is there an easy way I can show the amounts together? i.e debits from our GL that match the amount from the credits in the bank statement? And vice versa? Particuluars in columns B and G would not be unique.
Your help would be much appreciated.
Regards

[FoxSeaLady]

Hi

As an accountant I suggest that matching amounts in a bank reconciliation is prone to errors without reference to a unique identifier. I also acknowledge that this may not be possible for all transactions such as bank fees, deposits and EFT transactions to automate a cross match between two sets of records based solely on one criteria (amount) is risky.

I would suggest as an alternative to ease the difficulty created by the volume is to reconcile (preferably manually) on a daily basis with an appropriate investigation and correction performed daily. This will ease the volume to be matched at any time. Even on a daily basis I would not recommend automated matching based only on amounts.

Just my thought

Cheers

[nimv1]

Thanks for your reply. I have been doing it manually so far, but it is taking too long.
Is there a way I can align the amounts rather than flick from top to bottom of screen?

Regards

[FoxSeaLady]

Hi

Manually matching amounts does run the risk of offsetting two amounts although similar are not the same transaction. If however you continue on this basis I have re-structure your spreadsheet to align amounts.
Determining the best way to manually do it for future use will depend on how you load the data. Is it automated or manually input?

In restructuring (Restructure tab)
a) I have converted the credits in the GL to negative
b) Converted the Bank Debits to negative (as they should have appeared in your GL)
c) Converted the Bank Credits to positive (as they should have appeared in your GL)

Then sorted the table by amount. This will place similar amounts above/below so making it easier to manually delete if the
amounts match.
There is a formula driven using Sumif which adds the items according to whether they are GL Debits/Credits or
Bank statement Debits/Credits.

This should provide a reconciliation with no variance but I did notice that the data supplied did not reconcile
in total so that should be the problem. I am unable to attempt to reconcile the variance given the number of entries presented.
After you have matched Type reconciled in column B , cut and paste to Reconciled tab.
Considering the size of some of the transactions it would be unwise to only match on value and based on the
number of items dating back months there is a risk that if a transaction either by the bank or in the
GL is not followed up and corrected loss could occur or significant errors could be reported in your financial statements.

Does this help your issue?
Cheers

[nimv1]

Hi there,
Thanks for this, I am fairly new with excel and will review the spreadsheet created to get my head round and understand.
Thanks again for your time. If I have any questions, I will let you know. Your help and time has been much appreciated.
The amounts in the reconciled tab, have they come from the statement I sent?

Regards

[FoxSeaLady]

Hi

Glad to be of some help.

I used your data and just moved it around

Cheers

[Markmasix]

Hi, you can create a pivot table to filter these data
a) Keep the original amount
b) Create a new column and Convert the Bank Debits to negative
c) Convert the Bank Credits to positive

Using the pivot table, move the original amount and reference to column field and the debits & credits column to calculation of sum. After that, you can apply filter on the amount field which sum of the debits & credits are equal to zero.

By using this method, you can get your result in a few seconds