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 name in a column across the page. What tool do I use to do this?

 

I would also like to alternate colors of the rows of duplicate names to keep track of the courses each learner is required to take as in the example I have attached.

 

I greatly appreciate your input as this is a report I run and create monthly.

 

Kind regards -

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

6 Replies

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

Resources