SOLVED

How to group automatically in excel

Iron Contributor

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 !

20 Replies

@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 xD

Hi @Sekoleyte 

 

With Power Query (file attached):

 

_Screenshot.png

Hi @L z.
Can you share the file please to check it?

Edited my previous reply to add the file - Sorry

Hi again @L z. 

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?

 

Sekoleyte_2-1653237825175.png

Sekoleyte_3-1653238309067.png

 

 

 

@Sekoleyte 

 

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

@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(...)

 

@Sekoleyte 

 

See Power Query WBS2 that dynamically adjusts to the max # of Levels

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  @L z. @mtarler 

 

@L z.  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?

 

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, @L z. 

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.

Sekoleyte_1-1653475629609.png

 

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.

Sekoleyte_0-1653477351034.png

 

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_0-1653475481120.png

 

@Sekoleyte 

No way with formulas or Power Query. Maybe with VBA but not sure...

@L z.
I edited my message if you read it earlier.
@Sekoleyte
VBA isn't my cup of tea. You'll have to wait for one expert to jump in - Sorry about that
best response confirmed by Sekoleyte (Iron Contributor)
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

 

@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.

Sekoleyte_0-1653544000564.png

and that QR code directed me to that site:

https://support.microsoft.com/en-us/sbs/windows/troubleshoot-blue-screen-errors-5c62726c-6489-52da-a...

 

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  @L z. @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.
1 best response

Accepted Solutions
best response confirmed by Sekoleyte (Iron Contributor)
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

 

View solution in original post