Accounting Reconcile Template

**wpbrian** · 10-02-2012, 06:54 PM

Need help with a basic accounting reconcile template....

I have 2 data lists (columns A-D and columns H-K) seperated by a zero-sum formula to see if there is a variance between the amounts paid and recieved. The issue with the list is that the customer names dont always match up due to timing of deposits and final payments. I currently have to manually align each customer name by row to match the 2 data lists. I have attached a workbook to see an example of this process. Sheet 1 is the raw data lists that I start the manual alignment with. Sheet 2 is the outcome when I am finished manually aligning the rows by customer name. The auto-sum of the entire zero-sum center column F is the total variance for the period of what was paid vs. recieved. Sheet 3 is the copy/paste of the uncleared rows (customers with a variance).

Ultimately, the end goal is the match up each customer and their respective transactions to see if there is a variance (outstanding balance due or credit to be returned). Any suggestions or direction to improve this process would be greatly appreciated.

Thank you.

-Brian

reconcile example.xlsx

**FDibbins** · 10-02-2012, 09:37 PM

i looked at your data, and your 1st set of names does not match exactly your 2nd set of names, eg Akos, and AKOS, D if these formats will always remain constant, we can probably work something out.

try this on sheet1, column L, copied down...
=SUMIF($C$3:$C$33,PROPER(LEFT(H3,FIND(",",H3,1))),$D$3:$D$33)

**wpbrian** · 10-03-2012, 11:50 AM

I am working on a way to extract the data in list 1 (columns A-D) to include the first initial of the first name after the ",". However the first name initial after the "," maybe be in a seperate column for example last name column C and first name initial column D and $amount column E (all other columns shift right etc...). As of now my report writer does not generate the first name initial after the "," for the data in list 1 but its still a work in progress.

Is it possible to automatically align the rows by matching the first 4 characters of column C and column H ?

I appreciate your time and help on this, thanks again!

**FDibbins** · 10-03-2012, 01:51 PM

i have made some changes to your table, moving your "2nd" table further to the right, and then creating a new "2nd" table, based on the old 1.

the way i am extracting/summing your values is taking the last name, before the ",", and comparing that with your 1st list of names (see yellow highlight.

1 small problem with only using the 1st 4 letters of a name, is that there is always a chance that 2 different last names could have the same 1st 4 letters (chan/chandler/chance etc). so i based it on the full last name

**wpbrian** · 10-03-2012, 02:29 PM

The amount pulled in column K seems to be incorrect. Column K should reflect the amount in column V.

Thanks again for your help. If you have any other suggestions for a simple reconcile process for this type of data list please let me know!