Macro needed to group data based on column location with multiple rollups

[lmoseley7]

This is going to be hard to explain, but here goes.

For any of you that are familiar with PeopleSoft, chartfields are organized into "trees". Think of this as basically a file structure where you have a parent folder (node) and under that folder you have either additional folders or detail level values (leaves). I am able to copy the tree from PeopleSoft and paste it into Excel. Once in Excel, I determined that the levels of the items in the tree were denoted by leading spaces before the text. Therefore, if a node had three leading spaces, then the items within that node would have four leading spaces. Using some code that I got here from Rick Rothstein I was able to translate the leading spaces into semi-colons and then using text to columns, I can distribute the information in a manner that makes it easy to see where the rollups are. Here's where the problem comes in. Now I want to group the data so that the detail rolls up to the parent node and then that node rolls up to its parent as well. I have no idea how to accomplish this in code and manually performing this is very time consuming.

My only thought would be to note which column is the first column with data on each row. Let's say that at the bottom, the first column with data was column D. D would equate to a 4. The macro could then go up until it finds a number less than 4, its parent, and then group those lines up to the parent row. It would keep going up the rows, grouping lower numbers into higher numbers until it reached the top. The macro would have to make a couple of passes though, because at some point, all the 3's would need to roll up to a 2 and so on.

Although in theory something like I mentioned might work, it seems very clunky to me (and I don't even know what I'm doing) so hopefully there is a simpler method that would work that I'm not aware of. Any help anyone would give would be appreciated. I'd be more than happy to email a portion of this spreadsheet to anyone willing to give it a try. I'm including a very simplistic excerpt below. The actual file has about 2,000 rows and data is in 12 columns.

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 19"]
<tbody>[TR]
[TD]INF_TECHNOLOGY[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]IT_BUS_INTELLIGENCE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1206305000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1601010310[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]IT_VOICE_TELECOM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1206040600[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]IT_INTERNET_PROJECT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1206100000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1601060000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PC_SYS_IMPLEMENT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1206090000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]INFORMATION_TECH[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1201060000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1201060111[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1201060333[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1201060555[/TD]
[/TR]
</tbody>[/TABLE]

 

[lmoseley7]

I was able to write a formula to return the position of the data in a row, no huge feat obviously. The issue is going to come in when I'm trying to group the rows based on the level of the detail and the fact that level 4's will group up to a 3 and that 3 will group up with other 3's until we reach a 2, etc. I assume the code for this would be really complicated, but I'm always amazed at some of the slick ways people come up with for handling issues. Often, what is done is something I would have never known about or thought to do. All I can say is the lucky person that figures this out will be the hero of many here at my company.

 

[lmoseley7]

Here's a thought. First I write a formula to determine what column the data resides in (already done). In the rollups of the data, there will only be one "1". There could be several of any number other than "1". The macro could first group all rows below 1 to roll up into the 1 row. The row below row 1 would then become the anchor row; in this case let's say it is a "2". The code would then test each row below the 2 looking for another 2. If it never finds another 2, it would group until the end of the data, if it finds another 2, it would group everything above that row into the anchor 2. After each grouping, the focus would move to the next row down from the anchor row. If the number was higher than the previous number, we are in a subgroup, if the number is equal to the previous, we are in a sibling group. If the number is lower, we are in a group that is only related at a higher level (aunt or uncle group?).

So in laymans terms, this is what the code should be doing: Set my anchor cell, what's my value? (ex. 6) Okay, what's the value of the cell directly below the anchor (7). Now keep testing cells until the value isn't greater than 7, adding the rows to an array within the code. Once the last row is found, group all the rows together. Once that grouping is done, we need to move down two rows. The first row we know is a 7, so we are looking for the next non-seven row to start the next grouping, otherwise we might create unnecessary groups.

See the table below; this is an example of the rollups where the 1 is the ultimate parent node, 2 rolls up to 1, 3 to 2 and so on. As you can see, at least to this level of the data, level 8 is the lowest level. We have two level 7s shown. Everything would roll up to one, everything greater than 2 would roll up to that level, until we hit another 2, etc. All the 7s and 8s shown here would roll up to the 6 shown, etc.[TABLE="width: 20"]
<tbody>[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[lmoseley7]

Definitely could have used this macro today. I have no doubt somebody on this forum can make this happen, I'm just assuming these posts haven't caught their eye yet. Will someone help please?

 

[lmoseley7]

Another call to arms for those macro geniuses out there.

 

[ssully87]

Do you have an example spreadsheet? I'm currently working on rolling up data in my report as well. are there just 3 columns that need to be rolled up?

 

[lmoseley7]

Do you have an example spreadsheet? I'm currently working on rolling up data in my report as well. are there just 3 columns that need to be rolled up?

First, thanks for your reply. In the example I was working with, I manually added all the groupings and it came to 7 or 8 layers deep. I could provide you with a sample spreadsheet if you'll PM me your email address.

 

[lmoseley7]

If anyone wanted to get me a Christmas gift, solving this macro issue would be a great idea!

 

[lmoseley7]

So yesterday I was manually grouping another set of data like I had mentioned in this thread before and I got to the point where it would no longer add grouping levels. Having never experienced that limitation before I wasn't sure what was going on. There was no error message or anything, Excel just stopped grouping. When I went to Google to see if there was a limit, I came across a post on another website where a user had written the exact code that I needed, but was having trouble with the 8-level grouping limit. After trying that code this morning, it is doing exacty what I need. You can find that code here: vba - Is there a constraint on the depth level of grouping in Excel? - Stack Overflow

The code requires a "helper" column where column A includes a number representing the grouping level, as you'll see if you visitthe link above. The code is:

Sub Makro1()
Dim maxRow As Integer
Dim row As Integer
Dim groupRow As Integer
Dim depth As Integer
Dim currentDepth As Integer
 
maxRow = Range("A65536").End(xlUp).row
 
For row = 1 To maxRow
    depth = Cells(row, 1).Value
    groupRow = row + 1
    currentDepth = Cells(groupRow, 1).Value
    If depth >= currentDepth Then
       GoTo EndForLoop
    End If
    Do While currentDepth > depth And groupRow <= maxRow
        groupRow = groupRow + 1
        currentDepth = Cells(groupRow, 1).Value
    Loop
    Rows(row + 1 & ":" & groupRow - 1).Select
    Selection.Rows.Group
EndForLoop:
    Next row
End Sub