Excel Formula

Question

Hello,

I am an accounting student, and for my study project, I am looking to develop an application in Excel 2003 that will automate the work of an accountant, that is to say, the journal, general ledger, trial balance, balance sheet, and income statement. I have already completed the journal, but it is the transfer of data to the general ledger that poses a problem for me. Indeed, I am looking to transfer the dates, account titles, and respective amounts by indexing the account numbers.
For example, in the journal I have:

A B C D
Date Account Title Amount
Dec 03, 2005 601 purchase 3000000
Dec 15, 2005 411 customer 3000000
Dec 15, 2005 512 bank 5000000
Dec 15, 2005 521 cash 275000
Dec 22, 2005 512 bank 1000000
Dec 23, 2005 601 purchase 120000

I would like, for example, by indexing 512 to obtain:

Date Account Title Amount
Dec 15, 2005 512 bank 5000000
Dec 22, 2005 512 bank 1000000

I have already tried using the INDEX/MATCH functions, but I can only find the first row. Please help me find a formula or a macro that would allow me to perform this task.
PS: I am still a beginner in Visual Basic

Configuration: Windows XP Firefox 3.0.1

yakov · Accepted Answer

Crystal Reports is a report builder.

Another idea to delve deeper into what LatelyGeek is proposing...

When your automatic filters are activated on your journal sheet, and you've ensured that the data is sorted by account number, have you tried using subtotals on each account change... This way, your ledger will appear prominently with subtotals for each field... Then you can create views that display the journal upon request, or the ledger...

yakov · Answer

It's data management your business...
EXCEL is not a very practical data manager for this kind of stuff...
Do you necessarily have to do this with EXCEL?
Otherwise, have you tried presenting the result as a pivot table?

LatelyGeek · Answer

I would say that the simplest thing is to use the filter.

You select your header row, then Data/Filter/AutoFilter.

An "arrow" appears to the right of all your column headers; if you choose 512 with this arrow in your Account No. column, you will only see your bank transactions.

And if you then need to create a sheet with these transactions, you position yourself in the table, press Ctrl * to select everything, copy, and paste into another sheet.

This operation can easily be automated with a little VB.

--
Why complicate life to make it simple when it’s so easy to make it complicated?

giorgioo · Answer

Thank you LatelyGeek for your solution, it's quite practical but it will require me to create a new sheet for each account and that's quite a bit of work. Nevertheless, could you give me some tips for automation with VBA? I'll try with that.
In any case, thank you because this is the first time I've seen a bit of light since I've been stuck on this.

yakov · Answer

Of course you can do this with EXCEL and a few well-placed lines of programming, but wouldn't it be simpler (and therefore faster) with ACCESS, if of course you have it... Honestly, I am used to programming heavy tables in EXCEL and I find that the time you will spend on it is not worth the result... If you insist on using EXCEL to enter the data, can't you consider importing into ACCESS (a very simple manipulation) the journals which would then offer you well-placed views for the General Ledgers, Trial Balances, Income Statements, and Balance Sheets??? Thinking about it, this is really just a different presentation of the data, and nothing more, so a simple report to print differently according to needs...

Of course, this reflection only holds if you have ACCESS and some knowledge of it.

Now if you want to do this in EXCEL, you just need a code that loops through each line of your journal and reports the data into the corresponding sheets.

Have you also tried using the VLOOKUP function?

yakov · Answer

I continue my reflection....

If you consider that the accounting documents you want to obtain are different Views of the same data, can you simply think of creating Reports with Crystal Report that would present the data for each desired document?

giorgioo · Answer

I have ACCESS but I don’t really know how to use it. On the other hand, I may not fully master EXCEL, but I manage pretty well!
I’ve already tried using VLOOKUP, but in my case it’s not as accurate as INDEX/MATCH since it returns a value in the same row of a column as the specified value, so for every new journal, I would have to recreate the formulas, whereas I want to create a set of standard Excel sheets to automate the basic work of an accountant in a small organization (journal, general ledger, balance sheet, income statement).
By using INDEX/MATCH, I get a result that gets closer to my goal. Indeed, by specifying my searched account number, the result shows me the first operation corresponding to that account number, but unfortunately just the first one. So if I could get the 2nd, the 3rd, up to the Nth, that would be a good solution!

By the way, what is Crystal Report??
Thank you in advance and have a great weekend!!

LatelyGeek · Answer

Here is a short module that you execute after selecting your account, which copies/pastes the data into a new sheet...

Sub CreateAccountSheet()

'
Selection.CurrentRegion.Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste

End Sub

--
Why make life complicated by doing simple things when it's so easy to make things complicated?

giorgioo · Answer

Ok! I'll give it a try. I'll let you know the result.

yakov · Answer

I'm continuing...

You're going to think I'm stubborn lol, but I'm really interested in your post so I'm going to keep rambling about it...

If I understood your situation correctly, you're entering your accounting data into a journal sheet (or sheets).

Then you want the General Ledger, Trial Balance, and Income Statement to be automatically generated... I'm on board with that, it shouldn't be too complicated to set up by programming everything in VBA since these are just different presentations of the data (and partial for the Income Statement)

But how are you going to handle your Balance Sheet???
That's already much more complicated to implement because there you can't just present data based on account numbers but rather group accounts under different headings....
Will you be able to manage with your VBA code or will you need to switch to something else???

I'm thinking about this to make sure you don't waste your time setting everything up only to realize it will get stuck on your Balance Sheet.....

But I'm sure you have the solution...

tenace · Answer

Hello, Just a question, wouldn't there be a solution with matrix formulas? Kev

yakov · Answer

What do you think of subtotals? (my second to last message)

giorgioo · Answer

Hi friends

I had to be absent and leave my job for a while because of the compositions.
I was inspired by LatelyGeek's first solution (8) and created a macro that sorts the accounts from the journal and then copies them to a dedicated space in my general ledger. Basically, we have for the accounts 101000; 103000 and 106000:

knowing that:

-A1:H20000 represents my entire journal whose column headers are: Date; Account; Labels; Debit/Date; Account; Labels; Credit

-jl_debit and jl_credit are the parts of my journal gathering respectively the debit and credit operations
-d_cplso is the area of the general ledger where I want to gather the debit operations of account '101000
-c_cplso is the area of the general ledger where I want to gather the credit operations of account '101000
and so on

sub class1()

Sheets("Journal").Select
Range("A1:H20000").Select
Selection.AutoFilter

'101000 cplso
Selection.AutoFilter Field:=2, Criteria1:="101000"
Application.Goto reference:="jl_debit"
Selection.Copy
Sheets("Gd livre").Select
Application.Goto reference:="d_cplso"
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=True, Transpose:=False
Sheets("mod JL").Select
Selection.AutoFilter Field:=2

Selection.AutoFilter Field:=6, Criteria1:="101000"
Application.Goto reference:="jl_credit"
Selection.Copy
Sheets("Gd liv").Select
Application.Goto reference:="c_cplso"
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=True, Transpose:=False
Sheets("mod JL").Select
Selection.AutoFilter Field:=6

'103000 cplpers
Selection.AutoFilter Field:=2, Criteria1:="103000"
Application.Goto reference:="jl_debit"
Selection.Copy
Sheets("Gd liv").Select
Application.Goto reference:="d_cplpers"
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=True, Transpose:=False
Sheets("mod JL").Select
Selection.AutoFilter Field:=2

Selection.AutoFilter Field:=6, Criteria1:="103000"
Application.Goto reference:="jl_credit"
Selection.Copy
Sheets("Gd liv").Select
Application.Goto reference:="c_cplpers"
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=True, Transpose:=False
Sheets("mod JL").Select
Selection.AutoFilter Field:=6

'106000 ecreev
Selection.AutoFilter Field:=2, Criteria1:="106000"
Application.Goto reference:="jl_debit"
Selection.Copy
Sheets("Gd liv").Select
Application.Goto reference:="d_ecreev"
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=True, Transpose:=False
Sheets("mod JL").Select
Selection.AutoFilter Field:=2

Selection.AutoFilter Field:=6, Criteria1:="106000"
Application.Goto reference:="jl_credit"
Selection.Copy
Sheets("Gd liv").Select
Application.Goto reference:="c_ecreev"
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=True, Transpose:=False
Sheets("mod JL").Select
Selection.AutoFilter Field:=6
end sub

Where it becomes complicated is that:
1- I would like to order Excel not to copy if it doesn't find a criterion
2- sometimes Excel in executing the macro copies the same entry multiple times into an area, I don't know what it's due to!

I hope you understand and will try to help me. Thank you in advance!

LatelyGeek · Answer

I propose a solution:

You add this at the beginning of your code:

Range("K1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(2,C[-7])"

In my example, it counts in cell K1 the number of numeric values contained in column D (7 columns before K - you can adapt it) once the filter is applied (SUBTOTAL function)

And then, you add each time:

Selection.AutoFilter Field:=6, Criteria1:="106000"
If Range("K1").Value <>0 then
Application.Goto reference:="jl_credit"
.
.
.
End If

For the other problem, I advise you to run your code step by step with the F8 key to see where it goes off the rails. (Alt F11 to open VBasic Editor, position yourself on the first line and press F8 for each line

--
Why make life complicated by making it simple, when it's so simple to make it complicated?

Excel Formula

14 réponses

Saracroche: the effective french app against phone spam

How to get the character µ on the android keyboard.

Texture issues in enshrouded

Access issue to my "contact me" profile

Windows spotlight is not working.

Black screen digital camera

Connecting rj45 cable with fiber

Sony bravia tv red led blinks 4 times

Free: associating with the freebox server in progress

Inuyasha in french