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
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!
mtarler Thank you.
To find WBS level, you found new and easier approach. That looks awesome.
For your macro solution i will check it detailed and try to understand and learn it. i wonder how it works.
But now i doubt something that goes wrong for my computer.
and that QR code directed me to that site:
https://support.microsoft.com/en-us/sbs/windows/troubleshoot-blue-screen-errors-5c62726c-6489-52da-a372-3f73142c14ad
A few days ago while i am checking your solutions, i got this message. After restart, i couldn't connect the internet and when i open the office it gave me an error message to repair and then it always closed itself. Then i gave my company computer to my IT. They couldn't solve the problem and they formatted my computer because they said that my windows doesn't work normal. Now i got this message in this morning when i open my computer and i could take this photo.
I am the only one who uses O365 in my company now. Others use Office 2019. Because i used O365 in my ex company, i insisted to have it individually. So they let me set up O365 instead of Office 2019. Our IT member suspects whether O365 cause it or not. I don't think it is because of O365. Did you experience same issue with your own computers? Do we have anything in those documents which can cause this situation? I don't want they take my O365 from me 😞
Riny_van_Eekelen Lorenzo mtarler
- I do not think that has to do with O365. In my experience, blue screen errors are due to driver or hardware issues. Other than that, I'm not much more help.
