Forum Discussion

LMesi1110's avatar
LMesi1110
Copper Contributor
Mar 28, 2020
Solved

Insert empty row between last names

Dear all,   I have a spreadsheet with a list of training courses for a group of learners. Each learner may have at least 20 courses to take. I would like to place an empty row between each last nam...
excel
  • LMesi1110's avatar
    LMesi1110
    Mar 29, 2020

    Hello and thank youExcelExciting 

     

    I do know how to obtain the design on the left. I want to create the design on the right. I have 86 people with different data  that I want to sort like this and right now I do it manually by inserting an empty row between last names and then highlight each person's data to make reading the report easier. I have multiple reports similar to this that I need to create monthly.

    I am so appreciative of your response to my question!

     

    Kind regards, Lisa Mesi

ExcelExciting's avatar
ExcelExciting
MVP
Mar 28, 2020

HI LMesi1110 

 

Is this what you are looking ?

 

 

pls check the last records.

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer.

 

  • LMesi1110's avatar
    LMesi1110
    Copper Contributor
    Mar 29, 2020

    Hello and thank youExcelExciting 

     

    I do know how to obtain the design on the left. I want to create the design on the right. I have 86 people with different data  that I want to sort like this and right now I do it manually by inserting an empty row between last names and then highlight each person's data to make reading the report easier. I have multiple reports similar to this that I need to create monthly.

    I am so appreciative of your response to my question!

     

    Kind regards, Lisa Mesi

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Mar 29, 2020

      LMesi1110 

      Table to the right

      could be generated by Power Query

      let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ColumnNames = Table.ColumnNames(Source),
    EmptyTable = Table.FromColumns({{null},{null},{null}}, ColumnNames),

    GroupByNames = Table.Group(
        Source,
        List.FirstN(ColumnNames,1),
        {{"Count", each Table.Combine({_,EmptyTable}), type table}}),
    RemoveUnused = Table.SelectColumns(GroupByNames,{"Count"}),
    ExpandTable = Table.ExpandTableColumn(
        RemoveUnused,
        "Count",
        ColumnNames, ColumnNames)
in
    ExpandTable

       

      After that let apply to the resulting table some background color, and two conditional formatting rules 

      first one to remove colors from empty rows, and second one with formula

      =MOD(COUNTIF($F$2:$F2,""),2)

      to apply another color to every other block.

      • LMesi1110's avatar
        LMesi1110
        Copper Contributor
        Mar 30, 2020

        Thank you so much!SergeiBaklan 

         

        This is quite a bit over my head. I am not familiar with Power Query and have done some searching on how to do this. Is there some level 1 training you can recommend to get to the point of following your suggestion?

         

Resources