May 22 2022 07:28 AM - edited Jan 02 2023 12:45 AM
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 !
May 22 2022 08:13 AM - edited May 22 2022 08:13 AM
@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.
May 22 2022 08:30 AM - edited May 22 2022 08:39 AM
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 xD
May 22 2022 08:37 AM
@Sekoleyte Yeah!, I misunderstood. Sorry.
May 22 2022 08:42 AM - edited May 22 2022 08:52 AM
May 22 2022 08:50 AM - edited May 22 2022 08:50 AM
Hi @Lorenzo
Can you share the file please to check it?
May 22 2022 08:53 AM
May 22 2022 09:55 AM - edited May 22 2022 09:56 AM
Hi again @Lorenzo
Thanks for your effort for my step 1 question. I checked your solution and i have a few questions.
Did you use advance editor? or is it possible to do this with only power query user interface?
If you use advance editor, for which steps did you use it?
When i check it in editor, i see that you are using a table like my helper columns. Is it not possible to find spaces at the beginning without defining a list that contains different number of spaces? maybe it can be more than 12 as well. For this, we have to change our list too.
I am not good at power query enough for now. Do you think that is easier compare to my formula solution?
May 22 2022 10:22 AM
Did you use advance editor? or is it possible to do this with only power query user interface?
Yes, used the Advanced Editor but it's probably doable with the UI
Is it not possible to find spaces at the beginning? maybe it can be more than 12 as well...
That was quick work based on the requirements you exposed. I'll think about it...
In the meantime please find attached 2 Excel 2021/365 formula options
May 22 2022 10:35 AM
@Sekoleyte Here is a cell formula solution:
=XLOOKUP(LEN(C2:INDEX(C:C,COUNTA(C:C))&".")-LEN(TRIM(C2:INDEX(C:C,COUNTA(C:C))&".")),WBS[Number of Spaces],WBS[WBS Level],"",-1,-1)
basically I find the # of spaces by subtracting the orig len - the len after using TRIM (which removes leading and trailing spaces). I & both cases with a "." to not include any potential spaces afterwards. I also used dynamic array to use the 1 formula to fill all the way down, but if this was in a table format you just need [@[Project Name]] instead of the C2:INDEX(...)
May 22 2022 12:23 PM
May 24 2022 11:47 PM - edited May 24 2022 11:58 PM
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?
May 25 2022 02:51 AM
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?
May 25 2022 03:49 AM - edited May 25 2022 04:30 AM
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.
May 25 2022 04:07 AM
No way with formulas or Power Query. Maybe with VBA but not sure...
May 25 2022 04:21 AM
May 25 2022 05:14 AM
May 25 2022 07:07 AM
Solution@Sekoleyte Interesting. So I found an easier and better way to do the WBS level:
=(SEARCH(LEFT(TRIM(B2),1),B2)-1)/2+1
This 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
May 25 2022 11:56 PM
@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:
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
May 26 2022 03:50 AM
May 25 2022 07:07 AM
Solution@Sekoleyte Interesting. So I found an easier and better way to do the WBS level:
=(SEARCH(LEFT(TRIM(B2),1),B2)-1)/2+1
This 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