Mar 27 2020 07:27 PM
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 -
Mar 28 2020 08:38 AM
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.
Mar 29 2020 08:37 AM
SolutionHello 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.
I am so appreciative of your response to my question!
Kind regards, Lisa Mesi
Mar 29 2020 10:32 AM
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.
Mar 30 2020 06:13 AM
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?
Mar 30 2020 06:27 AM
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.
Mar 30 2020 07:04 AM
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.
Mar 29 2020 08:37 AM
SolutionHello 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.
I am so appreciative of your response to my question!
Kind regards, Lisa Mesi