Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Create a pivot table combining data from 2 columns into one

0

I have a a double entry accounting file, which, in the same row, will record an account to Debit, Debit Amount and Account to Credit, Credit Amount. The Accounts listed in Account to Debit and Account to Credit are both data validation drop-downs and both refer to the same list . The transactions are formatted as a table with named columns.

All the transactions are listed in one sheet and I need a way to pull out all the transactions related to one account, whether it is a debit or a credit. SO that I can get a report for one account which will show all the debits and all the credits under that one account. 

The only solution I can think of is to have a "spare" table with a different name that duplicates all the data in the Transaction table, and then do a Pivot table with multiple data sources but it won't work because the "live" transaction table is always having rows deletd / added, inserted etc. 

Sample file is attached. Using Office 365.

Thanks in advance.

Answer
Discuss

Answers

0

You might filter on one column first and copy to another destination. Then filter on the other column and append the result to the first extract. Finally, sort the extracted data by date. If you automate the process it can be done at the click of a button.

This idea is implemented in the attached workbook. Double-clicking on any account name ("Account to Credit" or "Account to Credit" columns) will create an account. My code needs checking. I have a numb gut feeling that there might be a big logical error, like that it does make a difference if a credit or debit was clicked, but in my present state of mind (end of day) I can't find it. Actually, testing should be done with a smaller data sample, where one can follow each entry beginning to end. I don't have the data and I don't want to invest the time.

Before testing I recommend that you go through the code line by line and try to understand what's going on. I have added comments which will help you.

But there is one big change I made to your workbook which needs explanation. In essence, I used CodeNames to identify the worksheets. Every worksheet has two names. One is the tab name, the other the CodeName. In the VB Editor's project explorer the tab name is listed as Name, the CodeName as (Name). Both can be changed in the VBE's Properties Window. When you add a sheet to a workbook, Name and CodeName are the same, like Sheet1. VBE's Project Explorer window shows them as "Sheet1 (Sheet1)". It's a peculiarity of Excel that the CodeName has no parentheses here, as it does in the properties. 

Anyway, the point of all this is that the user can change the tab name but to change the CodeName access to the VBE is required. If you wish to transfer my code to to your own project you must create a worksheet with the CodeName "Account". The tab name doesn't matter because my code will change it anyway but the sheet must exist and its CodeName must be "Account". The idea is that you may create an account and your workbook will store it until you create another one in its place by double-clicking on another account name. If you double-click on the same name the account will be refreshed.

I also assigned a CodeName to your GL account sheet. It's "GenLed". I did this because your tab name is too unwieldy. My code will not recognize your GL sheet by its tab name, only its CodeName. For the same reason my code assigns the constant name GLTbl to your table TABLEGENERALLEDGER. If you ever decide to change this name it must be changed in the code as well.

Note that there is a short event procedure in the GL worksheet's code module. This procedure responds to when you double-click on a cell with a account name in it. It calls the main procedure in the module TeachExcel which does all the work. If you transfer my code to your project both procedures most be copied to their respective locations.

My code creates an independent worksheet which can be modified whichever way you want without affecting your data. Therefore, if the basic structure is correct - meaning all entries relevant to an account are indeed extracted - juggling the data within the sheet is easy. Look at it from that angle when you test.

Discuss

Discussion

Thank you, as I see it though I will then have to delete all the unnecesary data each time. Eg. if I filter by all "Cash on HAnd" debits - then in the same row there are all sorts of related credits in different accounts, which I would need to manually delete. Similarly the other way around.  I would like the report to contain ONLY "cash on hand" debits and credits so I can see if the account is in balance.
Srixxon (rep: 2) Feb 27, '20 at 7:00 am
Does your system create instances where the debit or credit is different from the transaction value? The solution I posted presumes that there can be no such difference.
Variatus (rep: 4889) Feb 27, '20 at 8:02 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login