Subtotal without subtotalling?

[mudskipper]

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

There you go

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

[LisaContractorUmbrella]

Thanks Mudskipper you're a great help

[MPwannadecentincome]

OK I am experimenting with data, I see that you need to have totals for each employee for each month as well as totals for the each employee for all months - right?

So the first one you have in column D already and you want the second in column E.

How important is it to have those values in those columns, can there be somewhere else? What is it you need to do with this data afterwards?

[darrylmg]

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

But have you tried switching it off and back on again...

[LisaContractorUmbrella]

OK I am experimenting with data, I see that you need to have totals for each employee for each month as well as totals for the each employee for all months - right?

So the first one you have in column D already and you want the second in column E.

How important is it to have those values in those columns, can there be somewhere else? What is it you need to do with this data afterwards?

Yep you've got it. The data can't really be anywhere else as I need to be able to view names and reference numbers which are attached to the data. Once this is resolved I need to vlookup corresponding data on another worksheet and then compare the two figures to give a variance i.e. the difference in totals for each reference number on the two worksheets.

[lilelvis2000]

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

I see there is a decent solution to this. But anyway. Yes there are various ways to determine the last row of data so the loop would not be fixed.
You seem to be doing a lot of complex Excel work...might think about dropping a VBA training course leaflet to the manager. I think it would be worth it.

[LisaContractorUmbrella]

I see there is a decent solution to this. But anyway. Yes there are various ways to determine the last row of data so the loop would not be fixed.
You seem to be doing a lot of complex Excel work...might think about dropping a VBA training course leaflet to the manager. I think it would be worth it.

Ok thanks for that - yes, you're right a VBA training course would probably be a good idea

[eek]

I see there is a decent solution to this. But anyway. Yes there are various ways to determine the last row of data so the loop would not be fixed.
You seem to be doing a lot of complex Excel work...might think about dropping a VBA training course leaflet to the manager. I think it would be worth it.

Lisa is the manager...

[LisaContractorUmbrella]

Lisa is the manager...

Actually I'm the Managing Director I also love spreadsheets which is why I still get involved with this stuff (probably much to the annoyance of the team who'd far rather I was off playing golf or something )

[eek]

Actually I'm the Managing Director I also love spreadsheets which is why I still get involved with this stuff (probably much to the annoyance of the team who'd far rather I was off playing golf or something )

I was going to say manager and owner but then remembered that its part of your brother's SJD group.

Did you get your company horse in the end?