Excel for find accounting errors in the general ledger

P

pjhutchi

New Member
Joined
Mar 10, 2016
Messages
8
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
 
Sort by date Sort by votes
borkarrr said:
HI pjhutchi,
even im from an accounting background ;)
Please post the screenshot as it would make the situation easier to understand for us...
Click to expand...
[TABLE="width: 857"]
<colgroup><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]111230 - SUSPENSE ACCOUNT MUN (15/16)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Code[/TD]
[TD]Description[/TD]
[TD]IE[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Balance[/TD]
[TD]Mth[/TD]
[TD]Year[/TD]
[TD]Reference[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BFWD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/05/2015[/TD]
[TD]YMCA[/TD]
[TD]YMCA OF PERTH INC CREDIT ON OVER INVOICING ORIGINAL INVOICE SIREC1193 30/04/15 CREDIT[/TD]
[TD]09[/TD]
[TD="align: right"]30778.50[/TD]
[TD][/TD]
[TD="align: right"]30778.50[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]SCRREC0184[/TD]
[/TR]
[TR]
[TD="align: right"]18/05/2015[/TD]
[TD]YMCA[/TD]
[TD]YMCA OF PERTH INC CREDIT FOR OVER INVOICING IN ERROR ORIGINAL INVOICE SIREC1201 DATED 18/05/2015 CREDIT[/TD]
[TD]09[/TD]
[TD="align: right"]30778.50[/TD]
[TD][/TD]
[TD="align: right"]61557.00[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]SCRREC0185[/TD]
[/TR]
[TR]
[TD="align: right"]28/05/2015[/TD]
[TD]WAWA[/TD]
[TD]WATER CORPORATION WATER CONSUMPTION SWIMMING POOL AT CLAUDE ST LOT 416 WATER CONSUMPTION[/TD]
[TD]09[/TD]
[TD="align: right"]847.77[/TD]
[TD][/TD]
[TD="align: right"]62404.77[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]9007674666[/TD]
[/TR]
[TR]
[TD="align: right"]10/06/2015[/TD]
[TD][/TD]
[TD]Move water usage charges (14/15) for 412 Conroy St to Suspense. Move water usage charges (14/15) for 412 Conroy St to Suspense.[/TD]
[TD]09[/TD]
[TD="align: right"]41.16[/TD]
[TD][/TD]
[TD="align: right"]62445.93[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]1516-01.25[/TD]
[/TR]
[TR]
[TD="align: right"]15/06/2015[/TD]
[TD][/TD]
[TD]Creditors Accrued Expenses 2015/16 Creditors Accrued Expenses 2015/16[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-37090.91[/TD]
[TD="align: right"]25355.02[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]1415-12.51[/TD]
[/TR]
[TR]
[TD="align: right"]15/06/2015[/TD]
[TD][/TD]
[TD]Creditors Accrued Expenses 2015/16 Creditors Accrued Expenses 2015/16[/TD]
[TD]09[/TD]
[TD="align: right"]37090.91[/TD]
[TD][/TD]
[TD="align: right"]62445.93[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]REV1415-12.51[/TD]
[/TR]
[TR]
[TD="align: right"]30/06/2015[/TD]
[TD][/TD]
[TD]Recognise committed funds from purchase of motor vehicle - purchase order 680. Recognise committed funds from purchase of motor vehicle - purchase order 680.[/TD]
[TD]09[/TD]
[TD="align: right"]37090.91[/TD]
[TD][/TD]
[TD="align: right"]99536.84[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]1415-12.56[/TD]
[/TR]
[TR]
[TD="align: right"]30/06/2015[/TD]
[TD][/TD]
[TD]Creditors Accrued Expenses 2015-16 Creditors Accrued Expenses 2015-16[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-37090.91[/TD]
[TD="align: right"]62445.93[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]1415-12.90[/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2015[/TD]
[TD][/TD]
[TD]To correct posting of Telstra bill on-charged to YMCA. To correct posting of Telstra bill on-charged to YMCA.[/TD]
[TD]09[/TD]
[TD="align: right"]34.56[/TD]
[TD][/TD]
[TD="align: right"]62480.49[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]1516-01.33[/TD]
[/TR]
[TR]
[TD="align: right"]06/07/2015[/TD]
[TD]YMCA[/TD]
[TD]YMCA WA ELECTRICITY BILL FOR AQUATIC CENTRE FOR THE PERIOD 17/03/2015 TO 20/04/2015[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-5427.32[/TD]
[TD="align: right"]57053.17[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]36806[/TD]
[/TR]
[TR]
[TD="align: right"]15/07/2015[/TD]
[TD]WESTPOWER[/TD]
[TD]SYNERGY ELECTRICITY - GROUPED ELECTRICUTY BILL FROM 03/04/2015-07/07/2015 GROUPED ELECTRICITY[/TD]
[TD]09[/TD]
[TD="align: right"]216.66[/TD]
[TD][/TD]
[TD="align: right"]57269.83[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]977854430[/TD]
[/TR]
[TR]
[TD="align: right"]15/07/2015[/TD]
[TD]WESTPOWER[/TD]
[TD]SYNERGY ELECTRICITY - GROUPED ELECTRICITY BILL FROM 03/04/2015 - 07/07/2015 TO CORRECT GST ERROR.[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-216.66[/TD]
[TD="align: right"]57053.17[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]CORRECTION[/TD]
[/TR]
[TR]
[TD="align: right"]15/07/2015[/TD]
[TD]WESTPOWER[/TD]
[TD]SYNERGY ELECTRICITY - GROUPED ELECTRICITY BILL FROM 03/04/2015 - 07/07/2015 TO CORRECT GST ERROR.[/TD]
[TD]09[/TD]
[TD="align: right"]196.96[/TD]
[TD][/TD]
[TD="align: right"]57250.13[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]CORRECTION[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015[/TD]
[TD]BAIRSTOWJ[/TD]
[TD]JANELLE BAIRSTOW UNIFORMS OVER 330.00 [/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-68.09[/TD]
[TD="align: right"]57182.04[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]36814[/TD]
[/TR]
[TR]
[TD="align: right"]21/07/2015[/TD]
[TD]DAYCARE[/TD]
[TD]DAY CARE CENTRE WATER USAGE 26/03/2015 - 27/05/2015 INCORRECTLY INVOICED[/TD]
[TD]09[/TD]
[TD="align: right"]41.16[/TD]
[TD][/TD]
[TD="align: right"]57223.20[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]1105[/TD]
[/TR]
[TR]
[TD="align: right"]21/07/2015[/TD]
[TD]KATPLAY[/TD]
[TD]KATANNING PLAYGROUP INC WATER USAGE 26/03/2015 - 27/05/2015[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-41.61[/TD]
[TD="align: right"]57181.59[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]36824[/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2015[/TD]
[TD]YMCA[/TD]
[TD]YMCA REFUND 20/07/15 :[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-61557.00[/TD]
[TD="align: right"]-4375.41[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]219235[/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2015[/TD]
[TD]YMCA[/TD]
[TD]YMCA WA ELECTRICITY BILL FOR PARK STREET PERIOD 19/05/2015 TO 15/06/2015 [/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-196.96[/TD]
[TD="align: right"]-4572.37[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]36829[/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2015[/TD]
[TD][/TD]
[TD]To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15. To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15.[/TD]
[TD]09[/TD]
[TD="align: right"]34.56[/TD]
[TD][/TD]
[TD="align: right"]-4537.81[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]1516-01.34[/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2015[/TD]
[TD][/TD]
[TD]To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15. To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15.[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-34.56[/TD]
[TD="align: right"]-4572.37[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]REV1516-01.34[/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2015[/TD]
[TD][/TD]
[TD]To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15. To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15.[/TD]
[TD]09[/TD]
[TD="align: right"]67.91[/TD]
[TD][/TD]
[TD="align: right"]-4504.46[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]1516-01.35[/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2015[/TD]
[TD][/TD]
[TD]To move to Suspense Playgroup water invoice with associated Water Corp expense posted to Suspense 14/15. To move to Suspense Playgroup water invoice with associated Water Corp expense posted to Suspense 14/15.[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-67.91[/TD]
[TD="align: right"]-4572.37[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]1516-01.36[/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2015[/TD]
[TD][/TD]
[TD]To correct posting of Day Care Centre credit note with associated invoice posted to Refunds Control 14/15. To correct posting of Day Care Centre credit note with associated invoice posted to Refunds Control 14/15.[/TD]
[TD]09[/TD]
[TD="align: right"]78.20[/TD]
[TD][/TD]
[TD="align: right"]-4494.17[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]1516-01.37[/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2015[/TD]
[TD][/TD]
[TD]To move to Suspense Katanning Playgroup water invoice with associated Water Corp expense posted to Suspense 14/15. To move to Suspense Katanning Playgroup water invoice with associated Water Corp expense posted to Suspense 14/15.[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-78.20[/TD]
[TD="align: right"]-4572.37[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]1516-01.38[/TD]
[/TR]
[TR]
[TD="align: right"]28/07/2015[/TD]
[TD]MCLEODS[/TD]
[TD]MCLEODS REFUND 28/07/15 :[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-12136.02[/TD]
[TD="align: right"]-16708.39[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]219369[/TD]
[/TR]
[TR]
[TD="align: right"]28/07/2015[/TD]
[TD]MCLEODS[/TD]
[TD]MCLEODS REFUND 28/07/2015 :[/TD]
[TD]09[/TD]
[TD="align: right"]12136.02[/TD]
[TD][/TD]
[TD="align: right"]-4572.37[/TD]
[TD]01[/TD]
[TD]15/16[/TD]
[TD]222867[/TD]
[/TR]
</tbody>[/TABLE]


Guys sorry I could not do any better, the DR column is "E" and the CR Column is "F". If the figure in the DR column sums to $0 with any individual figure in the CR column then I want to know. Ultimately if they do sum to $0 then I would like that row to delete and so then all that would be left is transactions that are not balanced. Or possibly id the unbalanced transactions where to be highlighted in red.
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
[TABLE="width: 662"]
<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 3"]111230 - SUSPENSE ACCOUNT MUN (15/16)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Code[/TD]
[TD]Description[/TD]
[TD]IE[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BFWD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]01/05/2015[/TD]
[TD]YMCA[/TD]
[TD]YMCA OF PERTH INC CREDIT ON OVER INVOICING ORIGINAL INVOICE SIREC1193 30/04/15 CREDIT[/TD]
[TD]09[/TD]
[TD="align: right"]30778.50[/TD]
[TD][/TD]
[TD="align: right"]30778.50[/TD]
[/TR]
[TR]
[TD="align: right"]18/05/2015[/TD]
[TD]YMCA[/TD]
[TD]YMCA OF PERTH INC CREDIT FOR OVER INVOICING IN ERROR ORIGINAL INVOICE SIREC1201 DATED 18/05/2015 CREDIT[/TD]
[TD]09[/TD]
[TD="align: right"]30778.50[/TD]
[TD][/TD]
[TD="align: right"]61557.00[/TD]
[/TR]
[TR]
[TD="align: right"]28/05/2015[/TD]
[TD]WAWA[/TD]
[TD]WATER CORPORATION WATER CONSUMPTION SWIMMING POOL AT CLAUDE ST LOT 416 WATER CONSUMPTION[/TD]
[TD]09[/TD]
[TD="align: right"]847.77[/TD]
[TD][/TD]
[TD="align: right"]62404.77[/TD]
[/TR]
[TR]
[TD="align: right"]10/06/2015[/TD]
[TD][/TD]
[TD]Move water usage charges (14/15) for 412 Conroy St to Suspense. Move water usage charges (14/15) for 412 Conroy St to Suspense.[/TD]
[TD]09[/TD]
[TD="align: right"]41.16[/TD]
[TD][/TD]
[TD="align: right"]62445.93[/TD]
[/TR]
[TR]
[TD="align: right"]15/06/2015[/TD]
[TD][/TD]
[TD]Creditors Accrued Expenses 2015/16 Creditors Accrued Expenses 2015/16[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-37090.91[/TD]
[TD="align: right"]25355.02[/TD]
[/TR]
[TR]
[TD="align: right"]15/06/2015[/TD]
[TD][/TD]
[TD]Creditors Accrued Expenses 2015/16 Creditors Accrued Expenses 2015/16[/TD]
[TD]09[/TD]
[TD="align: right"]37090.91[/TD]
[TD][/TD]
[TD="align: right"]62445.93[/TD]
[/TR]
[TR]
[TD="align: right"]30/06/2015[/TD]
[TD][/TD]
[TD]Recognise committed funds from purchase of motor vehicle - purchase order 680. Recognise committed funds from purchase of motor vehicle - purchase order 680.[/TD]
[TD]09[/TD]
[TD="align: right"]37090.91[/TD]
[TD][/TD]
[TD="align: right"]99536.84[/TD]
[/TR]
[TR]
[TD="align: right"]30/06/2015[/TD]
[TD][/TD]
[TD]Creditors Accrued Expenses 2015-16 Creditors Accrued Expenses 2015-16[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-37090.91[/TD]
[TD="align: right"]62445.93[/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2015[/TD]
[TD][/TD]
[TD]To correct posting of Telstra bill on-charged to YMCA. To correct posting of Telstra bill on-charged to YMCA.[/TD]
[TD]09[/TD]
[TD="align: right"]34.56[/TD]
[TD][/TD]
[TD="align: right"]62480.49[/TD]
[/TR]
[TR]
[TD="align: right"]06/07/2015[/TD]
[TD]YMCA[/TD]
[TD]YMCA WA ELECTRICITY BILL FOR AQUATIC CENTRE FOR THE PERIOD 17/03/2015 TO 20/04/2015[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-5427.32[/TD]
[TD="align: right"]57053.17[/TD]
[/TR]
[TR]
[TD="align: right"]15/07/2015[/TD]
[TD]WESTPOWER[/TD]
[TD]SYNERGY ELECTRICITY - GROUPED ELECTRICUTY BILL FROM 03/04/2015-07/07/2015 GROUPED ELECTRICITY[/TD]
[TD]09[/TD]
[TD="align: right"]216.66[/TD]
[TD][/TD]
[TD="align: right"]57269.83[/TD]
[/TR]
[TR]
[TD="align: right"]15/07/2015[/TD]
[TD]WESTPOWER[/TD]
[TD]SYNERGY ELECTRICITY - GROUPED ELECTRICITY BILL FROM 03/04/2015 - 07/07/2015 TO CORRECT GST ERROR.[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-216.66[/TD]
[TD="align: right"]57053.17[/TD]
[/TR]
[TR]
[TD="align: right"]15/07/2015[/TD]
[TD]WESTPOWER[/TD]
[TD]SYNERGY ELECTRICITY - GROUPED ELECTRICITY BILL FROM 03/04/2015 - 07/07/2015 TO CORRECT GST ERROR.[/TD]
[TD]09[/TD]
[TD="align: right"]196.96[/TD]
[TD][/TD]
[TD="align: right"]57250.13[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015[/TD]
[TD]BAIRSTOWJ[/TD]
[TD]JANELLE BAIRSTOW UNIFORMS OVER 330.00 [/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-68.09[/TD]
[TD="align: right"]57182.04[/TD]
[/TR]
[TR]
[TD="align: right"]21/07/2015[/TD]
[TD]DAYCARE[/TD]
[TD]DAY CARE CENTRE WATER USAGE 26/03/2015 - 27/05/2015 INCORRECTLY INVOICED[/TD]
[TD]09[/TD]
[TD="align: right"]41.16[/TD]
[TD][/TD]
[TD="align: right"]57223.20[/TD]
[/TR]
[TR]
[TD="align: right"]21/07/2015[/TD]
[TD]KATPLAY[/TD]
[TD]KATANNING PLAYGROUP INC WATER USAGE 26/03/2015 - 27/05/2015[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-41.61[/TD]
[TD="align: right"]57181.59[/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2015[/TD]
[TD]YMCA[/TD]
[TD]YMCA REFUND 20/07/15 :[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-61557.00[/TD]
[TD="align: right"]-4375.41[/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2015[/TD]
[TD]YMCA[/TD]
[TD]YMCA WA ELECTRICITY BILL FOR PARK STREET PERIOD 19/05/2015 TO 15/06/2015 [/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-196.96[/TD]
[TD="align: right"]-4572.37[/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2015[/TD]
[TD][/TD]
[TD]To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15. To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15.[/TD]
[TD]09[/TD]
[TD="align: right"]34.56[/TD]
[TD][/TD]
[TD="align: right"]-4537.81[/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2015[/TD]
[TD][/TD]
[TD]To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15. To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15.[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-34.56[/TD]
[TD="align: right"]-4572.37[/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2015[/TD]
[TD][/TD]
[TD]To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15. To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15.[/TD]
[TD]09[/TD]
[TD="align: right"]67.91[/TD]
[TD][/TD]
[TD="align: right"]-4504.46[/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2015[/TD]
[TD][/TD]
[TD]To move to Suspense Playgroup water invoice with associated Water Corp expense posted to Suspense 14/15. To move to Suspense Playgroup water invoice with associated Water Corp expense posted to Suspense 14/15.[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-67.91[/TD]
[TD="align: right"]-4572.37[/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2015[/TD]
[TD][/TD]
[TD]To correct posting of Day Care Centre credit note with associated invoice posted to Refunds Control 14/15. To correct posting of Day Care Centre credit note with associated invoice posted to Refunds Control 14/15.[/TD]
[TD]09[/TD]
[TD="align: right"]78.20[/TD]
[TD][/TD]
[TD="align: right"]-4494.17[/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2015[/TD]
[TD][/TD]
[TD]To move to Suspense Katanning Playgroup water invoice with associated Water Corp expense posted to Suspense 14/15. To move to Suspense Katanning Playgroup water invoice with associated Water Corp expense posted to Suspense 14/15.[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-78.20[/TD]
[TD="align: right"]-4572.37[/TD]
[/TR]
[TR]
[TD="align: right"]28/07/2015[/TD]
[TD]MCLEODS[/TD]
[TD]MCLEODS REFUND 28/07/15 :[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-12136.02[/TD]
[TD="align: right"]-16708.39[/TD]
[/TR]
[TR]
[TD="align: right"]28/07/2015[/TD]
[TD]MCLEODS[/TD]
[TD]MCLEODS REFUND 28/07/2015 :[/TD]
[TD]09[/TD]
[TD="align: right"]12136.02[/TD]
[TD][/TD]
[TD="align: right"]-4572.37[/TD]
[/TR]
[TR]
[TD="align: right"]28/07/2015[/TD]
[TD]MCLEODS[/TD]
[TD]MCLEODS REFUND 28/07/2015 :[/TD]
[TD]09[/TD]
[TD][/TD]
[TD="align: right"]-11032.75[/TD]
[TD="align: right"]-15605.12[/TD]
[/TR]
[TR]
[TD="align: right"]28/07/2015[/TD]
[TD]WAWA[/TD]
[TD]WATER CORPORATION WATER USAGE DAYCARE CENTRE 412 CONROY ST 27.05.2015 TO 27.07.2015 WATER USAGE[/TD]
[TD]09[/TD]
[TD="align: right"]4.12[/TD]
[TD][/TD]
[TD="align: right"]-15601.00[/TD]
[/TR]
[TR]
[TD="align: right"]28/07/2015[/TD]
[TD]WAWA[/TD]
[TD]WATER CORPORATION WATER CONSUMPTION DUPLEX UNIT 61A CONROY ST 27.05.2015 TO 29.07.2015 WATER USAGE[/TD]
[TD]09[/TD]
[TD="align: right"]5.79[/TD]
[TD][/TD]
[TD="align: right"]-15595.21[/TD]
[/TR]
[TR]
[TD="align: right"]29/07/2015[/TD]
[TD]WAWA[/TD]
[TD]WATER CORPORATION WATER CONSUMPTION DUPLEX UNIT 1/6 HILL WAY WATER USAGE[/TD]
[TD]09[/TD]
[TD="align: right"]14.47[/TD]
[TD][/TD]
[TD="align: right"]-15580.74[/TD]
[/TR]
[TR]
[TD="align: right"]29/07/2015[/TD]
[TD]WAWA[/TD]
[TD]WATER CORPORATION WATER CONSUMPTION DUPLEX UNIT 2/6 HILL WAY 28.05.2015 28.07.2015 WATER SERVICE[/TD]
[TD]09[/TD]
[TD="align: right"]5.79[/TD]
[TD][/TD]
[TD="align: right"]-15574.95[/TD]
[/TR]
[TR]
[TD="align: right"]29/07/2015[/TD]
[TD]WAWA[/TD]
[TD]WATER CORPORATION WATER USAGE 3L CROSBY ST 28.05.2015 TO 28.07.2015 WATER USAGE[/TD]
[TD]09[/TD]
[TD="align: right"]52.06[/TD]
[TD][/TD]
[TD="align: right"]-15522.89[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Unknown
ABCDEFG
1111230 - SUSPENSE ACCOUNT MUN (15/16)
2
3DateCodeDescriptionIEDebitCreditBalance
4BFWD0.00
501/05/2015YMCAYMCA OF PERTH INC CREDIT ON OVER INVOICING ORIGINAL INVOICE SIREC1193 30/04/15 CREDIT0930778.5030778.50
618/05/2015YMCAYMCA OF PERTH INC CREDIT FOR OVER INVOICING IN ERROR ORIGINAL INVOICE SIREC1201 DATED 18/05/2015 CREDIT0930778.5061557.00
728/05/2015WAWAWATER CORPORATION WATER CONSUMPTION SWIMMING POOL AT CLAUDE ST LOT 416 WATER CONSUMPTION09847.7762404.77
810/06/2015Move water usage charges (14/15) for 412 Conroy St to Suspense. Move water usage charges (14/15) for 412 Conroy St to Suspense.0941.1662445.93
915/06/2015Creditors Accrued Expenses 2015/16 Creditors Accrued Expenses 2015/1609-37090.9125355.02
1015/06/2015Creditors Accrued Expenses 2015/16 Creditors Accrued Expenses 2015/160937090.9162445.93
1130/06/2015Recognise committed funds from purchase of motor vehicle - purchase order 680. Recognise committed funds from purchase of motor vehicle - purchase order 680.0937090.9199536.84
1230/06/2015Creditors Accrued Expenses 2015-16 Creditors Accrued Expenses 2015-1609-37090.9162445.93
1301/07/2015To correct posting of Telstra bill on-charged to YMCA. To correct posting of Telstra bill on-charged to YMCA.0934.5662480.49
1406/07/2015YMCAYMCA WA ELECTRICITY BILL FOR AQUATIC CENTRE FOR THE PERIOD 17/03/2015 TO 20/04/201509-5427.3257053.17
1515/07/2015WESTPOWERSYNERGY ELECTRICITY - GROUPED ELECTRICUTY BILL FROM 03/04/2015-07/07/2015 GROUPED ELECTRICITY09216.6657269.83
1615/07/2015WESTPOWERSYNERGY ELECTRICITY - GROUPED ELECTRICITY BILL FROM 03/04/2015 - 07/07/2015 TO CORRECT GST ERROR.09-216.6657053.17
1715/07/2015WESTPOWERSYNERGY ELECTRICITY - GROUPED ELECTRICITY BILL FROM 03/04/2015 - 07/07/2015 TO CORRECT GST ERROR.09196.9657250.13
1816/07/2015BAIRSTOWJJANELLE BAIRSTOW UNIFORMS OVER 330.0009-68.0957182.04
1921/07/2015DAYCAREDAY CARE CENTRE WATER USAGE 26/03/2015 - 27/05/2015 INCORRECTLY INVOICED0941.1657223.20
2021/07/2015KATPLAYKATANNING PLAYGROUP INC WATER USAGE 26/03/2015 - 27/05/201509-41.6157181.59
2124/07/2015YMCAYMCA REFUND 20/07/15 :09-61557.00-4375.41
2224/07/2015YMCAYMCA WA ELECTRICITY BILL FOR PARK STREET PERIOD 19/05/2015 TO 15/06/201509-196.96-4572.37
2324/07/2015To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15. To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15.0934.56-4537.81
2424/07/2015To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15. To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15.09-34.56-4572.37
2524/07/2015To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15. To move to Suspense Day Care Centre credit note with associated invoice posted to Refunds Control 14/15.0967.91-4504.46
2624/07/2015To move to Suspense Playgroup water invoice with associated Water Corp expense posted to Suspense 14/15. To move to Suspense Playgroup water invoice with associated Water Corp expense posted to Suspense 14/15.09-67.91-4572.37
2724/07/2015To correct posting of Day Care Centre credit note with associated invoice posted to Refunds Control 14/15. To correct posting of Day Care Centre credit note with associated invoice posted to Refunds Control 14/15.0978.20-4494.17
2824/07/2015To move to Suspense Katanning Playgroup water invoice with associated Water Corp expense posted to Suspense 14/15. To move to Suspense Katanning Playgroup water invoice with associated Water Corp expense posted to Suspense 14/15.09-78.20-4572.37
2928/07/2015MCLEODSMCLEODS REFUND 28/07/15 :09-12136.02-16708.39
3028/07/2015MCLEODSMCLEODS REFUND 28/07/2015 :0912136.02-4572.37
3128/07/2015MCLEODSMCLEODS REFUND 28/07/2015 :09-11032.75-15605.12
3228/07/2015WAWAWATER CORPORATION WATER USAGE DAYCARE CENTRE 412 CONROY ST 27.05.2015 TO 27.07.2015 WATER USAGE094.12-15601.00
3328/07/2015WAWAWATER CORPORATION WATER CONSUMPTION DUPLEX UNIT 61A CONROY ST 27.05.2015 TO 29.07.2015 WATER USAGE095.79-15595.21
3429/07/2015WAWAWATER CORPORATION WATER CONSUMPTION DUPLEX UNIT 1/6 HILL WAY WATER USAGE0914.47-15580.74
3529/07/2015WAWAWATER CORPORATION WATER CONSUMPTION DUPLEX UNIT 2/6 HILL WAY 28.05.2015 28.07.2015 WATER SERVICE095.79-15574.95
3629/07/2015WAWAWATER CORPORATION WATER USAGE 3L CROSBY ST 28.05.2015 TO 28.07.2015 WATER USAGE0952.06-15522.89
Sheet1
 
Upvote 0
Hi, sorry for the late reply--

ok so to clear suspense accounts and ledger scrutiny heres what i do to find "matching" entries

Dr Cr Match Found? Row No?
45 36 #N/A
51 27 #N/A
25 44 #N/A
36 98 1

the formula i entered is =MATCH(THE AMOUNT IN DEBIT COULMN YOU WOULD LIKE TO SEARCH IN CREDIT COLUMN,CREDIT COLUMN WITH LOCKED CELL ARRAY PRESS F4,0)

meaining of formula is see amt in debit column eg- 45 go in credit column and show me at which row it is appearing--answer is n/a meaning not found

search for 36? and it said match found , at row no 1

You can combine it with index formula to also pull another cell so eg combining it with index formula you can "pull" name of party written against row no 1
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Trial Balance, INDEX/MATCH, Multiple Criteria, and SPILL
Replies
2
Views
468
Cubist
Cubist
L
  • Solved
Trouble running an access query in excel vba
Replies
2
Views
186
Legal Tender
L
W
  • Question Question
Excel VBA - Check For Duplicates and Return the Count on a Large Data Set
Replies
4
Views
354
Kaper
Kaper
L
  • Question Question
Index match working but not all information being brought in
Replies
3
Views
517
Peter_SSs
Peter_SSs
I
  • Question Question
VLOOKUP Maybe???
Replies
2
Views
619
Alex Blakenburg
A

Forum statistics

Threads
1,224,505
Messages
6,179,153
Members
452,891
Latest member
JUSTOUTOFMYREACH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top