Forum Discussion
How to group automatically in excel
Hi all.
I need your valuable help again and i am really excited for your solutions.
I am taking an export data from a programme that has Work Breakdown Structure. Then i want to create a grouped list in this WBS. To do it, firstly, i thought that i have to determine the WBS level, then i have to create a grouped list in this WBS. So i have 2 questions here.
🙂
1. How can I find how many spaces a cell contains at the beginning?
For ex: " Apples are delicious" contains 2 spaces at the beginning of the cell, so result should be 2.
(you will see my IFS function to do this in the file that i shared with you, but i wonder easier solutions with the shorter formula.)
2. How can i create groups according to WBS level automatically?
The file i shared with you that includes result that i want to have. But i did it manually. Ä° am after to create this grouped list easily according to WBS level automatically.
I am open to all kind of solutions like formulas, pivot tables, power query or VBA. I generally use formulas and rarely power query and pivot tables. I can learn VBA as well, if you have a solution for this.
Note: In the file that i shared with you, i created activity names in shape that " Xth WBS Level Activity Name Y" to fit with the WBS levels. Real names don't include WBS level in the activity names.
I will stay tuned here for your amazing solutions.
Thanks in advance my dear heros !
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
20 Replies
Hi guys.
My windows was broken while i answer to you. So i couldn't write here for a few days.
For 1st question, it looks we have new and easy approaches. I also want to share last thing and easiest way to do it with you. That can be used in case we are sure that there is not extra spaces between words and after words.
=(LEN([@[Activity Name]])-(LEN(TRIM([@[Activity Name]]))))/2+1
Thanks for your contribution so far Riny_van_Eekelen Lorenzo mtarler
Lorenzo All your solutions are amazing. I tried to understand your solutions detailed and i will start to use lamda and power query more. Your solutions are truly a lesson. I will check your solutions again, and will try to use them. Thanks for your informing.
mtarler You remind us there can be really different and easier approaches for this kind problems.
And to create an array in shape (a cell)&":"&(formula) looks different to use because when we give an array it is in shape that (a cell)&":"&(another cell), but in your solutions formula doesn't give a cell. it give a result (or results for office 365 solutions). i saw it somewhere else and i will try to understand it as well.
Our community members. We solved how to create wbs levels easiliy but we are still searching for an amazing solutions for our 2nd and main problem.
How to create a grouped list automatically according to WBS level in excel? Do you know any macro or power query solution for this or etc?
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 Counting leading spaces is not what would come into my mind when I look at the file you uploaded. Why not just look for the occurrence of numbers 1 to 7 in the first 15 characters if the WBS string. Then you could use something like:
=SUM((IFERROR(FIND({1,2,3,4,5,6,7},B3),15)<14)*COLUMN($A$1:$G$1))...to calculate the level in column A, without the need for the helper table. File attache with the above formula tin column C.
Hi Riny_van_Eekelen
Thanks for your effort.
We don't look for occurence of numbbers because they are names i created to prevent confusion but it made a confusion xD
I think you missed my note that i have added my discussion since I guessed that the namings I have given may be misleading. it was that:
"Note: In the file that i shared with you, i created activity names in shape that " Xth WBS Level Activity Name Y" to fit with the WBS levels. Real names don't include WBS level in the activity names."
I mean real activity names don't include WBS level. I don't know if i say it wrong or not in English. Think it like that, instead of " 2nd WBS Level Activity Name", it is actually " Construction Works". Hope i didn't misunderstood what you want to say xDSekoleyte Yeah!, I misunderstood. Sorry.
