Forum Discussion
Insert empty row between last names
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
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
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.
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?
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
within it will be link on videos related to the tutorial.
Many thanks!SergeiBaklan
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.