Subtotal without subtotalling?

[LisaContractorUmbrella]

If it's a one-off and you suspect it's just excel crashing, I'd try doing the same
on Libreoffice or openoffice to see if they work any better.

Have tried a couple of times and get the same result - when there was less data it would take around 45 minutes to enter the subtotals so am pretty sure it's just shear volume combined with nested Vlookup functions which I understand take up a lot of processing power

[LisaContractorUmbrella]

A Employee ID B Month C Amount D Total for Month (Calc: =IF(AND(A2=A1,B2=B1),0,C2)

1235 1 25.00
1235 1 15.00 40.00
1235 2 25.00 25.00
1235 3 125.00 125.00
2569 2 25.00
2569 2 15.00 40.00
2569 3 300.00
2870 3 25.00 325.00

What I need is a calculation in column E which will sum Column D at each change of reference in column A

I spent ages lining up all those numbers which now look fine whilst I am editing but all group together when I post

[woohoo]

A Employee ID B Month C Amount D Total for Month (Calc: =IF(AND(A2=A1,B2=B1),0,C2)

1235 1 25.00
1235 1 15.00 40.00
1235 2 25.00 25.00
1235 3 125.00 125.00
2569 2 25.00
2569 2 15.00 40.00
2569 3 300.00
2870 3 25.00 325.00

What I need is a calculation in column E which will sum Column D at each change of reference in column A

I spent ages lining up all those numbers which now look fine whilst I am editing but all group together when I post

I must be reading this wrong, but why not create a pivot table and group on Column A and subtotal that. Would give you the same result. But I am stupid as you can tell by my previous comments.

[lilelvis2000]

Unfortunately the formulas need to remain

That's fine. Just trying to determine if its all the shear volume of calculations that are causing the problem or a problem with the subtotaling.

See it as a workaround not a solution.

It could be made into a macro too.

[LisaContractorUmbrella]

I must be reading this wrong, but why not create a pivot table and group on Column A and subtotal that. Would give you the same result. But I am stupid as you can tell by my previous comments.

Once I have the figure per reference number I need to enter the same formula in another workbook which then needs to be compared to this one - as far as I know this isn't possible with a pivot table. Plus total data would be 26+ columns and 10000+ rows so I may have the same problem with Excel falling over

[lilelvis2000]

A Employee ID B Month C Amount D Total for Month (Calc: =IF(AND(A2=A1,B2=B1),0,C2)

1235 1 25.00
1235 1 15.00 40.00
1235 2 25.00 25.00
1235 3 125.00 125.00
2569 2 25.00
2569 2 15.00 40.00
2569 3 300.00
2870 3 25.00 325.00

What I need is a calculation in column E which will sum Column D at each change of reference in column A

I spent ages lining up all those numbers which now look fine whilst I am editing but all group together when I post

Might not be able to do it with a formula but easily done with a macro.

[LisaContractorUmbrella]

That's fine. Just trying to determine if its all the shear volume of calculations that are causing the problem or a problem with the subtotaling.

See it as a workaround not a solution.

It could be made into a macro too.

There are already 5 macro's attached to the file - that's probably not helping

[LisaContractorUmbrella]

Might not be able to do it with a formula but easily done with a macro.

tell me more

[lilelvis2000]

tell me more

How familiar are you with loops?

I'm terrible with pseudo code but it will go something like this (I'm sure someone on here will find all sorts of errors and provide a better version.)

var subtotal = 0
var breakValue
//get the first row breakValue and its values
breakvalue = sheet[0][A]
subtotal = sheet[0][D]
sheet[0][E] = subtotal
loop i = 2 to 100 //say
if breakvalue <> sheet[i][A] then
subtotal = 0
breakvalue = sheet[i][A]
end if
subtotal = subtotal + sheet[i][D]
sheet[i][E] = subtotal
next


Something like that will do it.

[LisaContractorUmbrella]

How familiar are you with loops?

I'm terrible with pseudo code but it will go something like this (I'm sure someone on here will find all sorts of errors and provide a better version.)

var subtotal = 0
var breakValue
//get the first row breakValue and its values
breakvalue = sheet[0][A]
subtotal = sheet[0][D]
sheet[0][E] = subtotal
loop i = 2 to 100 //say
if breakvalue <> sheet[i][A] then
subtotal = 0
breakvalue = sheet[i][A]
end if
subtotal = subtotal + sheet[i][D]
sheet[i][E] = subtotal
next


Something like that will do it.

Not familiar with loops at all Quite happy to record macro's but not really any good at writing them! Can kind of understand what you've written - will this still work if the number of rows vary? I can't give a definitive end point