Forum Discussion

Sekoleyte's avatar
Sekoleyte
Iron Contributor
May 22, 2022
Solved

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...
excel
Formulas and Functions
Macros and VBA
office 365
  • mtarler's avatar
    mtarler
    May 25, 2022

    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

     

Sekoleyte's avatar
Sekoleyte
Iron Contributor
May 22, 2022

Hi Lorenzo
Can you share the file please to check it?

Lorenzo's avatar
Lorenzo
Silver Contributor
May 22, 2022
Edited my previous reply to add the file - Sorry
  • Sekoleyte's avatar
    Sekoleyte
    Iron Contributor
    May 22, 2022

    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?

     

     

     

     

    • mtarler's avatar
      mtarler
      Silver Contributor
      May 22, 2022

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

       

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      May 22, 2022

      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

Resources