No idea how to title...Sorting in Excel to include group header?

Related1

New Member
Joined
May 22, 2017
Messages
21
Hi! I'm not sure how to phrase my question which makes hard to search for an answer....Can I sort by a grouping under a heading?

I have a sheet that I want to filter (by person), but I want the subheadings (Project name) to be included with the filtering. I can only explain by pictures :rolleyes:

The original spreadsheet:

bhio8g.jpg


When I filter for Holly to see her tasks, I see this (it removes the grouping header):

2qty33a.jpg



I would like to see this so she knows which project the task belongs to.

aubhut.jpg


My only thought would be to add a column with the list of the project name,and create a custom filter which would filter 1st by the person's name and then by the project name.. Oh, that might work. Nope. It put the project names in alphabetical order. I'd like to keep them in the order I have them in. I feel like there is a really simple solution I'm missing here.

Any other ideas?

THANK YOU!!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Can you post a sample file to a third party site so that it can be manipulated. ie. Box.net, Dropbox.com, etc. I believe that this can be easily accomplished using Power Query but personally, I don't like having to try and re-create an existing file and guess as to the structure.
 
Upvote 0
something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#4472C4]Projects[/td][td=bgcolor:#4472C4]Stephen[/td][td=bgcolor:#4472C4]Holly[/td][td=bgcolor:#4472C4]Sullivan[/td][td][/td][td=bgcolor:#D9E1F2]Names[/td][td=bgcolor:#D9E1F2]Holly[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2]Project 1[/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Task 1[/td][td]X[/td][td]X[/td][td]X[/td][td][/td][td=bgcolor:#D9E1F2]Projects[/td][td=bgcolor:#D9E1F2]Tasks[/td][td=bgcolor:#D9E1F2]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2]Task 2[/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2]X[/td][td=bgcolor:#D9E1F2][/td][td][/td][td]Project 1[/td][td]Task 1[/td][td]X[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Task 3[/td][td][/td][td]X[/td][td][/td][td][/td][td][/td][td]Task 2[/td][td]X[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2]Task 4[/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2]X[/td][td=bgcolor:#D9E1F2][/td][td][/td][td][/td][td]Task 3[/td][td]X[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Task 5[/td][td][/td][td]X[/td][td][/td][td][/td][td][/td][td]Task 4[/td][td]X[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2] [/td][td=bgcolor:#D9E1F2][/td][td][/td][td][/td][td]Task 5[/td][td]X[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Project 2[/td][td][/td][td][/td][td][/td][td][/td][td]Project 2[/td][td]Task 6[/td][td]X[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2]Task 6[/td][td=bgcolor:#D9E1F2]X[/td][td=bgcolor:#D9E1F2]X[/td][td=bgcolor:#D9E1F2][/td][td][/td][td][/td][td]Task 8[/td][td]X[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Task 7[/td][td]X[/td][td][/td][td][/td][td][/td][td][/td][td]Task 9[/td][td]X[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2]Task 8[/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2]X[/td][td=bgcolor:#D9E1F2][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Task 9[/td][td]X[/td][td]X[/td][td]X[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2]Task 10[/td][td=bgcolor:#D9E1F2]X[/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]


Power Query then Pivot Table from Query table.
You can set Pivot Table according to your needs of course :)

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF = Table.AddColumn(Source, "Custom", each if Text.Contains([Projects], "Project") then [Projects] else null),
    ErrorOut = Table.RemoveRowsWithErrors(IF, {"Custom"}),
    FillD = Table.FillDown(ErrorOut,{"Custom"}),
    UOC = Table.UnpivotOtherColumns(FillD, {"Projects", "Custom"}, "Attribute", "Value"),
    Rename = Table.RenameColumns(UOC,{{"Projects", "Tasks"}, {"Custom", "Projects"}, {"Attribute", "Names"}})
in
    Rename[/SIZE]
 
Last edited:
Upvote 0
something like this?

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #4472C4"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Projects[/COLOR][/TD]
[TD="bgcolor: #4472C4"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Stephen[/COLOR][/TD]
[TD="bgcolor: #4472C4"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Holly[/COLOR][/TD]
[TD="bgcolor: #4472C4"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Sullivan[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #D9E1F2"]Names[/TD]
[TD="bgcolor: #D9E1F2"]Holly[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #D9E1F2"]Project 1[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Task 1[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD="bgcolor: #D9E1F2"]Projects[/TD]
[TD="bgcolor: #D9E1F2"]Tasks[/TD]
[TD="bgcolor: #D9E1F2"]Value[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #D9E1F2"]Task 2[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"]X[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD][/TD]
[TD]Project 1[/TD]
[TD]Task 1[/TD]
[TD]X[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Task 3[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Task 2[/TD]
[TD]X[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #D9E1F2"]Task 4[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"]X[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD][/TD]
[TD][/TD]
[TD]Task 3[/TD]
[TD]X[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Task 5[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Task 4[/TD]
[TD]X[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD][/TD]
[TD][/TD]
[TD]Task 5[/TD]
[TD]X[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Project 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Project 2[/TD]
[TD]Task 6[/TD]
[TD]X[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #D9E1F2"]Task 6[/TD]
[TD="bgcolor: #D9E1F2"]X[/TD]
[TD="bgcolor: #D9E1F2"]X[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD][/TD]
[TD][/TD]
[TD]Task 8[/TD]
[TD]X[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Task 7[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Task 9[/TD]
[TD]X[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #D9E1F2"]Task 8[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"]X[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Task 9[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #D9E1F2"]Task 10[/TD]
[TD="bgcolor: #D9E1F2"]X[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

YES! Thank you so much!!!!
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top