Multiple Subtotaling (Subtotal the subtotals and grand total it all)

I've attached a sample workbook of the raw data and the ultimate final look
What I need help with is getting the subtotals or running totals

I need the subtotal of each Office and each group within each office
Something like
Office = first office, officetotal = 0
group = first group, group total = 0
then some kind of loop that checks to see if the office or the group has changed

I've been playing with it, but just can't get it to work

3 sheets - Data sheet is the sheet to mess around with
RawData is the data (copy and paste over Data sheet)
Final is what they want it to look like

I can do the formatting, I just can't quite figure out how to get the different totals, plus 3 columns are manual entry by end user, after data is formatted, and those subtotals need formulas (not hard coded)
Thanks in advance for any guidance!

Sorry! The file was too large but I didn't notice the message. I had to delete some stuff to make it fit, but I think it will still be good enough to understand.
Thank you for any assistance!

Hi
Data is where the SQL statement puts the data and where the end user will eventually enter the manual numbers.

Final was just to show what they hoped it would look like

Nothing I've done comes close to working.

I've just started trying to use a helper column that would indicate each office change and another helper column (N & O) to indicate each group change. Thinking to count up # of rows for each group and and create formula with that info on an inserted blank line below each group.

If I can get that part, then the next would be to find each office change and add up the group subtotals.

I'm a little stuck on counting up and using the row count in a formula.

One problem is line 32, where office changes but group is same as below so I'm not finding it.

Do you think this new idea might work?

I can handle all the formatting, once the summing gets done.

This code tells me where things change

Please Login or Register  to view this content.

This code inserts a blank line each time group (or office) changes (change column # to 3 from 13 for office)

Please Login or Register  to view this content.

Hi, please ask all the questions you want - I'm so glad to have you helping!
The formatting, summing, inserting of rows, etc. should happen before the user sees the data so they see final product.

User will not enter rows, only data. User has option to enter data in columns I, J, L and also in the row labeled Manual Number (which would be instead of entering by client above in I, J, L). So Group Total would add in that manual number if used, otherwise, it will be same as subtotal.

All other columns/rows/cells will be locked.

If you could just help me figure out how to sum the groups and then how to sum the offices, I think I can get in all the other formatting, extra rows, etc. I'm just totally stuck on getting the subtotals of each group, each office and then a grand total at the bottom.
Thank you VERY MUCH!

Hi, John,

No, Final was just to show what it might look like in the end. Final won't really exist at all.

Everything happens on Data.

There is a command button that user clicks to retrieve the data. The SQL code pulls the data onto the Data worksheet, just like you see it in my sample. Then it gets manipulated from there.

Thanks,
Karin

They will re-run this every month. Data sheet will be completely cleared, the query run again, the formatting all re-done (as there may be more or less data than prior month). It will format automatically by calling any subroutines setup for formatting/subtotaling, etc. before user sees anything.

Code should be made to work on Data as Final will never really exist. We just hope Data will look like Final (approximately) when all coding is done.

I can do all the formatting, I just can't figure out how to get subtotals formulas.

If you can help me get a line after each group labeled Group Subtotal and the (dyanmic) formulas on it, and then a line after each Office, labeled Office Subtotals with (dynamic) formulas and, hopefully, a line at the end labeled Grand Totals with dynamic totals (total of each office subtotal), that would be awesome.

Using helper columns, or whatever it takes to get there. I've been struggling with this for 4 days.

Thank you!

I did finally get this to work - find the change and sums up

Please Login or Register  to view this content.

Hi, John,
Do you mean that Data sheet or the Data itself? (A bit confusing)
If you mean the actual data (not sheet), I'm not sure what you mean by reused.

Also, I've gotten a little further than I ever have before. I'd like to attach it but it's larger than I'm allowed. Is there any way around that?

I removed the final sheet to shrink size of file.
Run the subs in order and you'll see where I'm at.

I think I don't have a clue how to subtotal the offices from that point.

Perhaps this is not the best way to handle this, but...

Thanks!

I'll be back tomorrow, I have to go now

Hi, John,

I meant Data Sheet; I'd assume Data Sheet is cleared of all contents then new SQL data written to it for each new SQL import of data. But, I don't know that.

Yes, data sheet is completely cleared when it's re-run.

By the way, you're profile shows Excel 2007. Does this need to run JUST in Excel 2007 or does it need to run in earlier versions?

It has to run on 2003, nothing earlier.

Have a wonderful holiday with your family!

Thank you for any assistance!

Karin

Please see this link - it's interesting code and it works, but I'm not sure how I might translate it to fix my problem
http://www.vbaexpress.com/kb/getarticle.php?kb_id=115

John, this is FABULOUS!
I'm amazed - thank you so much!!!
I will test it more, but it seems to be working.
I can't possibly thank you enough for helping me with this.

Karin