Excel for find accounting errors in the general ledger

[pjhutchi]

Hi Guys and Gals,

While my work requires me to use excel to view and edit spreadsheets all the time I have not developed my skills past a beginners level. My current role has me trawling through the debits and credits in the general ledger accounts to find imbalances.

I know that there would be formulas and functions in excel that would allow me to do these checks in minutes rather than hours.

In the GL's there are a coloum of debits on one side and credits on the other. There can be hundreds and if not thousands of rows in these worksheets.

debits are positive say $100 while credits are negative -$100. What I want to do is for the cells in the debits to cross match on the credits and sum if they are $0 meaning that a cell on the debits side balances outwit ha cell on the credit side.Theoretically all that would be left to analyse then are the cells that are not balancing.

I am happy to email someone an example spreadsheet.

Help with this would be greatly appreciated as it could save me hours.

Thanks

 

[fiberboysa]

I am an accounts professional as well. the problem in you question is that you must have JV or a transaction number as well. Because many time one accounting entry is breaked up but they add up to balance debit and credit. So u must match the JV no. as well.
Well according to your situation use this formula in conditional formatting on both columns to see which values are not coming in debit of credit side.
I am supposing that debits are in column A and Credits are in Column B. Please change columns as per you situation and Rows as well. In column A use this conditional formatting...
=IF(SUMPRODUCT((-A1=$B$1:$B$6)*1)=0,TRUE,FALSE)
and use this in Column B
=IF(SUMPRODUCT((-B1=$A$1:$A$6)*1)=0,TRUE,FALSE)

 

[Peter_SSs]

Welcome to the MrExcel board!

I am happy to email someone an example spreadsheet.

Doing this would be a breach of #4 of the Forum Rules, which you may not have become familiar with yet but should do so now.

See A5 and section B of the Forum Use Guidelines for help with providing sample data.

 

[pjhutchi]

Great thanks I will give it a try

 

[pjhutchi]

No worries thanks for pointing this out.

 

[pjhutchi]

This is on the right track but not quite. Do you know how to post a sample here so that I am able to show you?

 

[pjhutchi]

I am an accounts professional as well. the problem in you question is that you must have JV or a transaction number as well. Because many time one accounting entry is breaked up but they add up to balance debit and credit. So u must match the JV no. as well.
Well according to your situation use this formula in conditional formatting on both columns to see which values are not coming in debit of credit side.
I am supposing that debits are in column A and Credits are in Column B. Please change columns as per you situation and Rows as well. In column A use this conditional formatting...
=IF(SUMPRODUCT((-A1=$B$1:$B$6)*1)=0,TRUE,FALSE)
and use this in Column B
=IF(SUMPRODUCT((-B1=$A$1:$A$6)*1)=0,TRUE,FALSE)

Here is a screen shot basically what I want to do is if the individual cells in the debit column zero out to an individual cell in the credit column then I want those columns to delete or to flag with a colour. This way theoretically I could get down to only a few transactions with I can investigate and reconcile.

 

[Peter_SSs]

For me, the link in post #7 just says "page not found".
In any case, an image isn't much good, as we cannot copy from an image to our own worksheet to test.

Do you know how to post a sample here so that I am able to show you?

Yes, I already pointed you to it.

See A5 and section B of the Forum Use Guidelines for help with providing sample data.

Example of one of those methods:

Excel Workbook
	A	B	C
1	CAT NO	Territory	Code
2	1001	UK	AA
3	1001	France	AB
4	1001	US	AC
5	1002	UK	AD
Lookup

 

[fiberboysa]

Where is the SS?
And if you will apply conditional formatting on both columns all the entries that are not coming in debt side or credit entries that are not coming in debit side will be highlighted with colour whatever u choose.

 

[borkarrr]

HI pjhutchi,
even im from an accounting background
Please post the screenshot as it would make the situation easier to understand for us...