Running balance table, monthly/year grid
 

Hi

Data
----------------------------------------------------
column1 column2 column3
1/1/1980 1000 collected
4/1/1981 -500 entered
6/2/1980 200 collected
4/2/1983 -1500 entered
7/2/1982 2100 collected
9/5/1980 -5000 entered
6/3/1980 2000 collected
-----------------------------------------------------
I want to list running balances like

1980 1981 1982
Jan (amount) (amount) (amount)
Feb (amount) (amount) (amount)
March (amount) (amount) (amount)
April (amount) (amount) (amount)

Thx.

Running balance table, monthly/year grid
 

You can do this with a sumproduct formula

Put in row 1 starting in column F the years 1980. Put in Column E the names
of the months starting in Row 2. Make the months names either all 3
characters (Jan, Feb,MAR) or spelled out completely (change MMM below to
MMMM). You have a mixture off both type month names in your data.

Put the following formula in cell F2

=SumProduct(--(year($A1:$A10000)=F$1),--($E2 =
text($A1:$A10000,"MMM")),$B1:$B10000)

"Sinner" wrote:

Hi

Data
----------------------------------------------------
column1 column2 column3
1/1/1980 1000 collected
4/1/1981 -500 entered
6/2/1980 200 collected
4/2/1983 -1500 entered
7/2/1982 2100 collected
9/5/1980 -5000 entered
6/3/1980 2000 collected
-----------------------------------------------------
I want to list running balances like

1980 1981 1982
Jan (amount) (amount) (amount)
Feb (amount) (amount) (amount)
March (amount) (amount) (amount)
April (amount) (amount) (amount)

Thx.

Running balance table, monthly/year grid
 

On Jun 17, 5:04*pm, Joel wrote:
You can do this with a sumproduct formula

Put in row 1 starting in column F the years 1980. *Put in Column E the names
of the months starting in Row 2. *Make the months names either all 3
characters (Jan, Feb,MAR) or spelled out completely (change MMM below to
MMMM). *You have a mixture off both type month names in your data.

Put the following formula in cell F2

=SumProduct(--(year($A1:$A10000)=F$1),--($E2 =
text($A1:$A10000,"MMM")),$B1:$B10000)



"Sinner" wrote:
Hi

Data
----------------------------------------------------
column1 * * column2 * * column3
1/1/1980 * * * 1000 * * * * collected
4/1/1981 * * * -500 * * * * *entered
6/2/1980 * * * 200 * * * * * collected
4/2/1983 * * * -1500 * * * * *entered
7/2/1982 * * * 2100 * * * * * collected
9/5/1980 * * * -5000 * * * * *entered
6/3/1980 * * * 2000 * * * * * collected
-----------------------------------------------------
I want to list running balances like

* * * 1980 * * *1981 * * * * *1982
Jan (amount) * (amount) * * (amount)
Feb (amount) * (amount) * * (amount)
March (amount) * (amount) * * (amount)
April (amount) * (amount) * * (amount)

Thx.- Hide quoted text -

- Show quoted text -

Joel with little modification to the ranges, I have the grid.
Thanks : )

Running balance table, monthly/year grid
 

Joel,

The OP is happy with your solution so this is not meant as a critique.

I had to make your ranges Absolute in Rows as well as Columns to stop them
indexing when I dragged the formula on the fill handle, (ie $A$1:$A10000),
otherwise it missed some data.

Even with only the years 1980 - 1983, with Jan - Dec it took about 5 seconds
to update on my system at every data entry because it was calculating
480,000 times.

It would seem that this is a case where you were right in the ~misc group
(http://tinyurl.com/5lamuk) whereby, as Bob Philips said, VBA *can* be
faster in some instances, although you seemed be making your statement in
~misc general as if VBA was *always* faster.

--
Regards,

Sandy

In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Joel" wrote in message
...
You can do this with a sumproduct formula

Put in row 1 starting in column F the years 1980. Put in Column E the
names
of the months starting in Row 2. Make the months names either all 3
characters (Jan, Feb,MAR) or spelled out completely (change MMM below to
MMMM). You have a mixture off both type month names in your data.

Put the following formula in cell F2

=SumProduct(--(year($A1:$A10000)=F$1),--($E2 =
text($A1:$A10000,"MMM")),$B1:$B10000)

"Sinner" wrote:

Hi

Data
----------------------------------------------------
column1 column2 column3
1/1/1980 1000 collected
4/1/1981 -500 entered
6/2/1980 200 collected
4/2/1983 -1500 entered
7/2/1982 2100 collected
9/5/1980 -5000 entered
6/3/1980 2000 collected
-----------------------------------------------------
I want to list running balances like

1980 1981 1982
Jan (amount) (amount) (amount)
Feb (amount) (amount) (amount)
March (amount) (amount) (amount)
April (amount) (amount) (amount)

Thx.