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

R

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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
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
sandy666 said:
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]
Click to expand...

YES! Thank you so much!!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Return and rank top 3 results (including ties) from VSTACK, FILTER, CHOOSECOLS, and SORT formula
Replies
8
Views
204
Peter_SSs
Peter_SSs
S
  • Solved
When Filter is applied, Excel jumps down multiple rows to (Blanks). How can Filter ignore blanks?
Replies
3
Views
453
Peter_SSs
Peter_SSs
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
387
Jagadeshrao
J
N
  • Question Question
Auto Sort for Leaderboard
Replies
0
Views
601
NTLKnight
N
S
  • Question Question
How do I sum without knowing the range?
Replies
1
Views
347
sGraham24
S

Forum statistics

Threads
1,224,813
Messages
6,181,114
Members
453,021
Latest member
Justyna P

Share this page

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