SOLVED

Insert empty row between last names

%3CLINGO-SUB%20id%3D%22lingo-sub-1260567%22%20slang%3D%22en-US%22%3EInsert%20empty%20row%20between%20last%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1260567%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20with%20a%20list%20of%20training%20courses%20for%20a%20group%20of%20learners.%20Each%20learner%20may%20have%20at%20least%2020%20courses%20to%20take.%20I%20would%20like%20to%20place%20an%20empty%20row%20between%20each%20last%20name%20in%20a%20column%20across%20the%20page.%20What%20tool%20do%20I%20use%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20also%20like%20to%20alternate%20colors%20of%20the%20rows%20of%20duplicate%20names%20to%20keep%20track%20of%20the%20courses%20each%20learner%20is%20required%20to%20take%20as%20in%20the%20example%20I%20have%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20greatly%20appreciate%20your%20input%20as%20this%20is%20a%20report%20I%20run%20and%20create%20monthly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%20-%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1260567%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1261229%22%20slang%3D%22en-US%22%3ERe%3A%20Insert%20empty%20row%20between%20last%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1261229%22%20slang%3D%22en-US%22%3E%3CP%3EHI%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F598728%22%20target%3D%22_blank%22%3E%40LMesi1110%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-03-28_18-37-22.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180150iF2815135FF12EB6C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222020-03-28_18-37-22.png%22%20alt%3D%222020-03-28_18-37-22.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Epls%20check%20the%20last%20records.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%22%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%20Answer%3C%2FSPAN%3E.%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1262549%22%20slang%3D%22en-US%22%3ERe%3A%20Insert%20empty%20row%20between%20last%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1262549%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20and%20thank%20you%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20know%20how%20to%20obtain%20the%20design%20on%20the%20left.%20I%20want%20to%20create%20the%20design%20on%20the%20right.%20I%20have%2086%20people%20with%20different%20data%26nbsp%3B%20that%20I%20want%20to%20sort%20like%20this%20and%20right%20now%20I%20do%20it%20manually%20by%20inserting%20an%20empty%20row%20between%20last%20names%20and%20then%20highlight%20each%20person's%20data%20to%20make%20reading%20the%20report%20easier.%20I%20have%20multiple%20reports%20similar%20to%20this%20that%20I%20need%20to%20create%20monthly.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22LMesi1110_0-1585496151703.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180332iF77BF20798DC7ADF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22LMesi1110_0-1585496151703.png%22%20alt%3D%22LMesi1110_0-1585496151703.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20am%20so%20appreciative%20of%20your%20response%20to%20my%20question!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%20Lisa%20Mesi%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1262689%22%20slang%3D%22en-US%22%3ERe%3A%20Insert%20empty%20row%20between%20last%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1262689%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F598728%22%20target%3D%22_blank%22%3E%40LMesi1110%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETable%20to%20the%20right%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20862px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180347i8920D06B54622F5D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ecould%20be%20generated%20by%20Power%20Query%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20ColumnNames%20%3D%20Table.ColumnNames(Source)%2C%0A%20%20%20%20EmptyTable%20%3D%20Table.FromColumns(%7B%7Bnull%7D%2C%7Bnull%7D%2C%7Bnull%7D%7D%2C%20ColumnNames)%2C%0A%0A%20%20%20%20GroupByNames%20%3D%20Table.Group(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20List.FirstN(ColumnNames%2C1)%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22Count%22%2C%20each%20Table.Combine(%7B_%2CEmptyTable%7D)%2C%20type%20table%7D%7D)%2C%0A%20%20%20%20RemoveUnused%20%3D%20Table.SelectColumns(GroupByNames%2C%7B%22Count%22%7D)%2C%0A%20%20%20%20ExpandTable%20%3D%20Table.ExpandTableColumn(%0A%20%20%20%20%20%20%20%20RemoveUnused%2C%0A%20%20%20%20%20%20%20%20%22Count%22%2C%0A%20%20%20%20%20%20%20%20ColumnNames%2C%20ColumnNames)%0Ain%0A%20%20%20%20ExpandTable%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20that%20let%20apply%20to%20the%20resulting%20table%20some%20background%20color%2C%20and%20two%20conditional%20formatting%20rules%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20726px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180348i6FB6E131F8DEBB82%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Efirst%20one%20to%20remove%20colors%20from%20empty%20rows%2C%20and%20second%20one%20with%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMOD(COUNTIF(%24F%242%3A%24F2%2C%22%22)%2C2)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eto%20apply%20another%20color%20to%20every%20other%20block.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 -

6 Replies
Highlighted

HI @LMesi1110 

 

Is this what you are looking ?

 

2020-03-28_18-37-22.png

 

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.

Best Response confirmed by LMesi1110 (New Contributor)
Solution

Hello and thank you@Faraz Shaikh 

 

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.

LMesi1110_0-1585496151703.png

I am so appreciative of your response to my question!

 

Kind regards, Lisa Mesi

Highlighted

@LMesi1110 

Table to the right

image.png

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 

image.png

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.

Highlighted

Thank you so much!@Sergei Baklan 

 

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?

 

Highlighted

@LMesi1110 

That's a lot of different level materials, but what I know and could recommend that's mainly next levels, not basic one.

 

You may start from Excel, in File->New find and open this tutorial

image.png

within it will be link on videos related to the tutorial.

Highlighted

Many thanks!@Sergei Baklan 

 

You have provided a direction to learn and apply your recommendation and I really appreciate your quick follow-up!

 

Have a great day and stay well.