Accounting Model (T Accounts)

Hi,

I have posted in the general forum (

HTML Code: 

) and it was suggested I post here so here goes…

What I require is a model which would take a data extract and post the line items to the correct account taking into account if it’s a debit or credit.

The steps that are required would be:
1. Identify all the unique GL accounts on the Data sheet column G
2. Create a new sheet for each GL and include certain headings

• Period
• Recd
• Trans.Date
• Post.Date
• Barcode
• G/L Acct
• Lennon_VAT
• Sap_VAT
• Ind.
• Amount
• Prof.Ctr
• Cost Ctr

3. Then assign each item on the data sheet to the correct account (and correct side of the account). I am unsure if it helps or not but each line item on the data sheet has a unique reference code (column C).

If all works it should all balance back to zero.

Can anyone help with a code please?

Hi,

Thanks for helping with the codes here.

@protonLeah - the debits/credits are on the wrong side. What part of the code do I need to change?

@xladept - is there any way in which the data can be arrange so there are no blank rows in the T accounts (as in the attached examples)?

A question for both is that when I have my actual data set on the "data" sheet it will have many many rows. Would I need to change anything or will it pick up the full range automatcially?

One other question (and I know I am moving the goal posts slightly but this isnt as essiential). Can either code be adapted to acocunt for another data set? e.g. If I had a data2 tab (which would be in the same format) could it also account for that in the accounts with the only difference being it shades the font a different colour, say red.

Any help on this or should I just be more patient?

Hi

@xladept Thank you, the code works and it now is presented how I visioned.

I have uploaded the working file but I have included a "Data2" sheet. Is there any way the code could be amended to include the 2nd data set but make it distingishable in the accounts? I know I am pushing my luck so tell me where to go if need be

@Leith I believe they are all expense accounts (operating expenses) the side in which they go on has already been determined in the column L of the data

Hi,

I have finally got the chance to view the code to run it! 

I believe its close.

Data 1
I have tested this and it all appears in the correct account and correct side, great news!

Data 2
Some of the GL accounts in columns M and N on the “Data 2” sheet may be unique. Is there any way in which it could create these accounts if they don’t exist also?

Other than that its looking really good! Thank you so much

I am slightly nervous when I come to use this in my main model as I do not understand VB. On the data1 sheet I have approx. 200 lines and then Data 2 sheet I have over 7000!! Would there be anything I need to amend or be aware of?

This works - I have run it for the sample data for just data1 and data2 and it does the job. Thank you!

Sorry, preserve what boxes? If its the formatting on the template sheet (which gets repliacted on a new sheet) then I would like to keep that. The template sheet on my actual model will contain loads more rows.

The only other thing I would want preseved really is somewhere to have a list of the accounts (sheets) its created?

xladept, you have done it!

This is excatly what I have described. I am going to spend the day tomorrow testing it all. I will soon know as it should all balance!

I will make as solved tomorrow once I have tested it fully. I do have a formula driven version which is work in progress so I can use the two to test each other. Your version is lightening fast.

Hi Xladept

I changed the "DATA" sheet slightly. In the main its the same but I shifted it along from orignally starting in column B to now starting in J.

What part of the code needs amending - its so hard for a noobie to work out.

I have uploaded an example file. In the example file your macro should create 40 different accounts and it will map approx 8000 transactions.

Thats strange as the model I have has GL acocunts. I have run it now - its a slow goer but progressing. I always knew it would take a while to create once run due to its complexity.

Jaslake is right. I apoligize if this caused you a lot of effort. I was torn between posting or not - I didnt want to lose what you had created which I knew worked but by not posting it meant I couldnt use the code previously supplied. Had I known it was a lot of effort on your behalf I wouldnt have asked.

I am honestly very gratful for all your help these last few days.

I will post back once its finished calculating. My laptop isnt the best but its chugging along, its taken over 45mins at the moment.

I tried running this and had to abort after a few hours. I will run it over night and report back

Really - how on earth did it run in 8 mins? Do you think its because of my laptop spec?

I am about to run it now but I am expecting it to take all night

Strange - I have started running it around 1.5 hours ago and its still going.

It does the first few sheets fine but then slows down when creating to last few sheets.

I just replied to it thanks!

Yes, only 50 sheets but its killing my machine. I have office 2010, its a Core i5 1.6ghz with 8gb of ram. It could only be my spec dont you think?

Hi,

So... I ran this overnight only to find out my laptop went into sleep mode! Arrrgghh!

I have tested it from the file you sent me and it all appears to be working great!!

If someone else could try the macro and see how long it takes to run that will be great...

Hi,

I am going to try this on a different work machine. They are the same spec so I will know if its a problem with my laptop or not.

I will report back