Bank account balance for each account in one table

**TomazJ** · 12-02-2022, 03:32 AM

I'm preparing one single table for multiple bank accounts. The table will be basic for pivot analysis, reports, etc...

The problem I have is that I would like to get new account balance at every new income/cost for each name (new rows, etc...) in one table. Because during the year the table will sure become huge (at least 3.000 rows), I need a solution as simple as possible to avoid excel being slow. I know how to make multiple tables for each account, but that means excel will become slow for sure...

Any simple ideas?sample.xlsx

**alansidman** · 12-02-2022, 03:42 AM

Here is power query solution to achieve ending balances

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

**bsalv** · 12-02-2022, 03:47 AM

=SUM([@income],-[@outcome],IFERROR(INDEX(F:F,AGGREGATE(14,6,ROW($C$4:$C13)/($C$4:$C13=[@Name]),1)),0))

**TomazJ** · 12-02-2022, 04:05 AM

Thanks both for ideas! Perfect!

What about excel speed after table will get bigger and bigger? Perhaps bsalv's solution can be a little bit more simple, therefore excel can handle it a little bit easier?

**alansidman** · 12-02-2022, 04:15 AM

The Power Query function is very fast and can handle up to millions of rows very quickly. Once set up, it calculates changes by selecting Refresh All.

**TomazJ** · 12-02-2022, 04:35 AM

Alan thanks for info.
I'll read stuff you wrote and try to learn as much as possible about PQ.

**bsalv** · 12-02-2022, 09:17 AM

indeed with 4.000 rows, there is a small delay.
if you change the recalculation to manual and then work with F9, that's the same as the refresh in PQ.
Another possibility is, with 4.000 rows/cells, copy the first 3.990 rows and paste them as values. Normally, these values 'll not change and therefore it's no problem to replace the formulas in those rows.

**TomazJ** · 12-02-2022, 02:29 PM

bsalv, one more question if I may...

...when I add one column on the left side, formatted as date, everything still works fine, but when I add another date row by pressing ctrl+d, excel returns there is problem with circular reference error.
As I googled, this is maybe a problem with aaggregate function number (14,6). The only solution is to manually extend table for another row, without ctrl+d. But that does bother me...

I plan to add some more columns on the right, so there will sure be more problem and not only with date column...
What would you do?

**bsalv** · 12-02-2022, 03:36 PM

i modified the formula (added some absolute addresses, included the actual row and, of course, used the 2nd largest row.
You didn't use a table, so i omitted that, because i'm not sure that works okay with CTRL+d

Please Login or Register  to view this content.

**alansidman** · 12-02-2022, 06:03 PM

In the attached I added a couple of new actions for Robin and then clicked on Refresh All. The data in my result table automatically updates. No need to change any coding with PQ

**mgramm** · 12-02-2022, 06:07 PM

Change you range into a table.
In the account balance column enter: =SUMPRODUCT(([Name]=[@Name])*([income]-[outcome])) and copy down.
This will give an account balance for each person at each data entry.

**josephteh** · 12-02-2022, 08:32 PM

Use SUMIF or SUMIFS, F5=SUMIF(C$5:C5,C5,D$5:D5)-SUMIF(C$5:C5,C5,E$5:E5), copy down.

**TomazJ** · 12-03-2022, 06:05 AM

mgramm
josephteh

I find your solutions perfect for seperate table, where daily account balance (for example if I need a table to check if balance is the same as shown at ATMs or web bank apps). But in basic table I can't see account balance what is needed for charts etc... Still working on solution how to use ctrl+d, but so far still nowhere...

Well in case I fail, I most likely will have to use bsalv's or alansidman's solution (late I still don't understand completely, but I work on that.

Anyway - you guys rock!

**bsalv** · 12-03-2022, 06:37 AM

with the formula of Josephteh

, i have no problems with CTRL+d, do you ?

**TomazJ** · 12-03-2022, 06:58 AM

You're right. Looks like it works. I have no idea what was wrong with my previous try..
..I'm gonna fill table with some old data, prety sure everything will be just fine. If not, I'll take a break out there in nature..

But I wonder if someone before me (of course, I'm the new guy here) had same question... Forum checked, but found nothing. How do you guys build a table for this kind of stuff (or Am I the only one, he he..)??

I'll be back...

EDIT:
No luck. Still changes balance at every new row.sample_1.xlsx

For Mary, column "I" is what I need.
Agrhhh.

**josephteh** · 12-03-2022, 07:26 AM

The formula should be =SUMIF($C$5:$C5,$C5,$D$5:$D5)-SUMIF($C$5:$C5,$C5,$E$5:$E5)
or =SUMIF(C$5:C5,C5,D$5:D5)-SUMIF(C$5:C5,C5,E$5:E5)

**bsalv** · 12-03-2022, 09:10 AM

i prefer to include the headerrow, because one can (probability ???)always insert a new first row ...

Please Login or Register  to view this content.

**josephteh** · 12-03-2022, 10:10 AM

Good thinking, bsalv!

**mgramm** · 12-03-2022, 12:56 PM

Try the table in the attached sheet. Keeps a total account balance with individual balances at top of sheet

**josephteh** · 12-03-2022, 09:41 PM

Good use of excel table structured cell references for cumulative sum total, mgramm!

**TomazJ** · 12-04-2022, 06:28 AM

Well, somehow I managed to do something..
..the table shows balance as I wanted, I'm just confused if my solution will make my table slow after 2.000 rows...

But still, I can simply delete formulas later. For analyse I don't need them anyway...sample_02.xlsx

**josephteh** · 12-04-2022, 06:47 AM

Don't refer to whole columns in your formula, it will slow down the workbook. Change E3=IF(F3="starting balance","",SUMIF(B$2:B3,B3,C$2:C3)-SUMIF(B$2:B3,B3,D$2:D3)).

**TomazJ** · 12-04-2022, 08:10 AM

Josephteh, thank you.

But if I add for example two rows as Paula, two times income 50€, then both new balances will be the same 170€ instead 120€ and 170€... and I don't want that.
And yes, I'm affraid my solution can cause poor table speed...

EDIT: Strange. It worked just fine when i copied down F3 one more time... Why is that so?

**josephteh** · 12-04-2022, 10:22 AM

No idea what you have done...

**bsalv** · 12-04-2022, 02:08 PM

can you add your problem-workbook here as attachment ?

**TomazJ** · 12-05-2022, 04:32 AM

sample_03.xlsx

I only added two rows, starting with ctrl+d in Column A.
As result I get two rows with balance +160€ instead one with +60 and another with +160€.

But when I once more copy E3 down, I get row15 -20€, row16 +60€ and row17 +160€.

What Am I doing wrong?

**JeteMc** · 12-07-2022, 11:02 AM

Try the following:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**TomazJ** · 12-10-2022, 06:16 AM

Originally Posted by JeteMc

Try the following:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

JeteMc, thanks, looks like this works just fine.
Hopefully it won't slow down table. Now I can continue with pivot tables.

Thanks to all of you for helping me!
Cheers!

**JeteMc** · 12-10-2022, 09:19 AM

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Re: Bank account balance for each account in one table

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] automatic entering Balance at every entry in my bank account

Bookmarks

Bookmarks

Posting Permissions