Macro to Group Rows and Columns With Many Layers And Add Subtotaling

Working with a large set of budget data (same attached) and trying to automate the grouping and entering of formulas (this is exported raw out of another database and so I have been manually entering formulas and grouping).

Nothing comes in colored the way it is, just raw data, but this is a snip from one of the files I did manually. Beige rows have numbers except in the total column which is a formula. Grey, Blue, and Orange rows ALL have formulas for every cell.

Basically, the formulas I am looking for are (simply put) as follows:


Brown lines in Col 1, Col 2, Col 3 sum UP into the orange subtotal/group line above. In this example, B5=SUM(B6:B7) B9=SUM(B10:B20), etc etc. This is true except for the total column (column E) which sums ACROSS, which in this example is E9=SUM(B9:D9), E14=SUM(B14:D14), etc, etc. The summing across for totals is true for every row regardless of color.

Orange lines sum UP into the corresponding blue line above it

Blue lines sum UP into the corresponding grey line above it

This dataset has something like 6 grey lines, 50 blue lines, and 100+ orange lines, so lots and lots of formulas.

I also, as shown in the sample, have them grouped so they will collapse by level (greys expand to show the blues below it, blues expand to show the orange below it, orange expands to show beige below).

Overall I think the structure is pretty simple, but I am trying to find an efficient starting point for how to count down the rows and figure out what formulas, coloring, and grouping should be applied. In effect I need to start at the top and look what would be the first grey line, then count to first blue, next blue, next blue, etc and then group so the collapse/expand button is on the top grey line. Do that for what would be all the grey lines, then move on to the first blue and grab all the oranges below it, and then to the oranges and grab all the beige below each of those.

The only reference data I have to differentiate what a line is is the text/numbers in column A. I can verbally explain what I need to do and understand just looking at raw un-formatted data what rolls up to what, but am having issues translating it to code that will do it for me.

Any help appreciate.

THanks,

So I have managed to get the coloring right. I'm posting from my phone now so I can add the code in a bit when I get to a computer, but I added a new column A as a helper column. There was enough distinction between the rows in the "Phase" column (now Col B) that I was able to tag the rows as a 1, 2, 3, or 4. 4 being just data, 4s roll up into a 3 above, 3s roll up into a 2 above, 2s roll up into a one above. Did that with For loops and an if statement, one cycle for each hierarchy level. Then another For/If to color the rows appropriately.

I've gotten that far, just trying to figure out how to count from one subtotal level, say a 3 in row 50, to the next 3 which might be in row 63. I would then select rows 51-62 and group them.

Originally Posted by Leith Ross

Hello Norcal1,

Since you have color coded the cells manually, is there any reliable way to identify which cell belongs to which group? Manually coloring or adding hierarchy numbers takes too much time. Automation depends on recognizable patterns.

Here is the code I used to insert a new column A and then use it as a helper column to label what "level" each row belongs to (1, 2, 3 or 4):

Please Login or Register  to view this content.

This gives me a level assignment in Column A, and then I used the letter in column A to assign colors to the rows.

Next step is to figure out how to count through the rows and grab the right rows to group. In the sample I attached again, what (I think) I should do is look down Column A row by Row. First it will find a "1" in Row 3, so it should then look down Column A until it finds the next "1" in row 77 and then select all rows BETWEEN those two rows (Select Rows 4 through 76) and group them. Do that for all the "1's" in Column A til the end of the data.

Next for Level "2", it should look down Column A again and in row 4 find a "2", then find the next "2" in row 8. It should select rows 5 through 7 and group them, then it would next find a "2" in row 8 and go down to find the next in row 42 and select rows 9 through 41 and group them, and on again.

Level 3 is slightly different because when you reach a "3" in Column A, the next "break" if you will could be a "1" OR a "2" OR a "3", but whatever the first one to come is that will always be the "end" of that group and beginning of the next.

If I were to ignore all the data and just look at the numbers in Column A, I've got to figure how to look down the column, find a number and store that row and then find the very next instance of that number and store that row, then just use the Rows().Select to grab that group of rows. From there I think I can figure out how to group and add formulas by rule.

The loop(s) i posted above seem to through my into and infinite loop but so far I have not been able to diagnose why. First time using a Do until so that's probably the deal.