Forum Discussion
How to group automatically in excel
Sekoleyte Interesting. So I found an easier and better way to do the WBS level:
=(SEARCH(LEFT(TRIM(B2),1),B2)-1)/2+1This version will NOT be affected by extra spaces at the end or in the middle 🙂
I also created a macro to automatically do the grouping based on whichever column you highlight:
Public Sub CreateGrouping() '(Optional c As Range) ', Optional UseSpaces = 0) Dim i, r, g As Integer Dim cc As Range If VarType(c) = vbObject Then Set cc = c Else Set cc = Selection End If r = Intersect(cc, ActiveSheet.UsedRange).Rows.Count 'use intersect incase they pass entire column For i = 1 To r If IsNumeric(cc.Cells(i, 1).Value2) Then g = cc.Cells(i, 1).Value2 j = i + 1 If cc.Cells(j, 1) > g Then Do While (cc.Cells(j + 1, 1).Value2 > g) And (j < r) j = j + 1 Loop Range(cc.Cells(i + 1, 1), cc.Cells(j, 1)).EntireRow.Group End If End If Next i End Sub
Hi Sekoleyte
How to create a grouped list automatically according to WBS level in excel?
This might be me but I'm afraid I don't understand what you exactly expect. Could you clarify with a couple of examples please?
Of course, Lorenzo
In document which i shared with you beforehand. it includes what i want to have indeed. In the picture what i shared below shows what i want to create. I made this grouping manually. I chose rows and created groups with Shift + Alt + Right combination, or you can reach it in Data menu as you see below.
For example, i chose all WBS level 7 (from 10.row to 17.row) and pressed S+A+R to create just one of those groups. This includes all subactivities in the " 6th WBS Level Activity Name 2" which is in 9.row. There are many of them in this grouping and it consumes too much time. To do it without mistake i did it like that:
1.st i created a group for WBS level 1 (Project Name), from row 3 to 196.
Then, for WBS level 2 ( 2nd WBS Level Activity Name), from row 4 to 196.
Then, Because we have 2 times WBS level 3 activities i did it
for ( 3rd WBS Level Activity Name 1), from row 5 to row 110.
and for ( 3rd WBS Level Activity Name 2) from row 112 to 196)
Then keep going it for other each level WBS levels and result shape is in the document.
We had 7 pieces of 4th WBS level, and i did it all for them. i did it 7 times for 4th level WBS.
When you choose 4.th level group you will see this. And each 4h level WBS includes all 5,6,7 WBS level under it if it has 5 or 6 or 7.
While i am doing i noticed ex document was wrong because i made a mistake. you can check previous trim document when you press 4 in grouping. you won't see row 137 because of my mistake. So i corrected it and share it here again.
It consumes too much time and it is open to make mistakes.
Somehow i want to create this complex grouping automatically according to WBS level instead of doing it manually. Hope, i could express it clearly.
Sekoleyte Interesting. So I found an easier and better way to do the WBS level:
=(SEARCH(LEFT(TRIM(B2),1),B2)-1)/2+1This version will NOT be affected by extra spaces at the end or in the middle 🙂
I also created a macro to automatically do the grouping based on whichever column you highlight:
Public Sub CreateGrouping() '(Optional c As Range) ', Optional UseSpaces = 0) Dim i, r, g As Integer Dim cc As Range If VarType(c) = vbObject Then Set cc = c Else Set cc = Selection End If r = Intersect(cc, ActiveSheet.UsedRange).Rows.Count 'use intersect incase they pass entire column For i = 1 To r If IsNumeric(cc.Cells(i, 1).Value2) Then g = cc.Cells(i, 1).Value2 j = i + 1 If cc.Cells(j, 1) > g Then Do While (cc.Cells(j + 1, 1).Value2 > g) And (j < r) j = j + 1 Loop Range(cc.Cells(i + 1, 1), cc.Cells(j, 1)).EntireRow.Group End If End If Next i End SubThank you so much. I could checked your solutions just now. I appreciate you for your approaches and solutions. I couldn't study on your macro yet but it works great! I like that kind of life saver solutions. I really respect you. Thank you for your help! Probably this will be my start to learning coding.
And thanks to all other helper guys!