filtering task list with sub task that follow parent task

[B-Man]

Im looking to do a job list in excel similar to the below and was hoping to be able to filter it by category and/or priority. but how do I get it to keep the sub tasks aligned with the task without duplicating the priority numbers and task for every subtask.
I feel like I could conditional format font to white if cell is = to the one above it (although would have have a trickier one for priority.) but then I would have to retype it for everything. is there some sort of easier solution?

Category	Priority	Task	Sub Task
House	1	Clean	Sweep Floors
			Wash Dishes
House	2	Install bench	assemble items
			screw bench top on
			seal benchtop to wall
Garden	1	Plant Trees	Plant natives
			plant fruit trees
			fertilise
			water

 

[GraH]

Hi B-Man
I have a question first. How is the raw data looking? Or is what you've posted how the data presents itself?

EDIT: a pivot shows data like so per default (tabular view, without subtotals).

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		Category	Priority	Task	Sub Task		Category	Priority	Task	Sub Task
2		House	1	Clean	Sweep Floors		Garden	1	Plant Trees	fertilise
3		House	1	Clean	Wash Dishes					plant fruit trees
4		House	2	Install bench	assemble items					Plant natives
5		House	2	Install bench	screw bench top on					water
6		House	2	Install bench	seal benchtop to wall		House	1	Clean	Sweep Floors
7		Garden	1	Plant Trees	Plant natives					Wash Dishes
8		Garden	1	Plant Trees	plant fruit trees			2	Install bench	assemble items
9		Garden	1	Plant Trees	fertilise					screw bench top on
10		Garden	1	Plant Trees	water					seal benchtop to wall
11
Sheet1

Cell Formulas
Range	Formula
B3:D3,B8:D10,B5:D6	B3	=B2

EDIT 2: The "first field" in the "rows" section of the picot, determines which labels are repeated.

Book1
	G	H	I	J
1	Task	Category	Priority	Sub Task
2	Clean	House	1	Sweep Floors
3				Wash Dishes
4	Install bench	House	2	assemble items
5				screw bench top on
6				seal benchtop to wall
7	Plant Trees	Garden	1	fertilise
8				plant fruit trees
9				Plant natives
10				water
Sheet1

 

[B-Man]

Raw Data is as it appears. although the sub task isn't on the same row as the task it starts a row below (oversight in typing it on the forum) I'm just starting so it can look like anything. ideally the less work involved in typing it up the better.
I guess it would look good to view like freshly edited below

I have been trying to play with groups which would work ok. If I filter it by category it seems to work but if I filter by priority the Header moves but the group seems to stay in the same place (same rows). so it appears by filtering rows out and hiding them it works but by rearranging them it breaks it. I like how it can hide the sub task and easy to expand it when wanting to view.

I'll have a look at pivot table. ideally I didn't want to have to retype all the data for each row but if it works its better than where im at the moment.

Category	Priority	Task/Subtask
House	1	Clean
		- Sweep Floors
		- Wash Dishes
House	2	Install bench
		- assemble items
		- screw bench top on
		- seal benchtop to wall
Garden	1	Plant Trees
		- Plant natives
		- plant fruit trees
		- fertilise
		- water

 

[GraH]

As a general tip, it is very helpful if your account details tell us a bit more on the version you use and your operating system. Also sharing data with XL2BB add-on is better then plane pasted data.

Maybe Power Query can offer you a solution, avoiding to type in data manually, as it has a feature called "Fill Down".

let
    Source = Excel.Workbook(File.Contents("G:\Uploads\TaskSubTaskFilter.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category", type text}, {"Priority", Int64.Type}, {"Task/Subtask", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "SubTask", each if [Category] = null then [#"Task/Subtask"] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Task", each if [SubTask] = null then [#"Task/Subtask"] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Category", "Priority", "Task"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([SubTask] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Task/Subtask"})
in
    #"Removed Columns"

I made a quick tutorial, but the animated JPEG is too big to share. Maybe I'll share it later if I can edit it.

 

[sandy666]

animated JPEG

rather GIF

 

[B-Man]

Hi B-Man
I have a question first. How is the raw data looking? Or is what you've posted how the data presents itself?

EDIT: a pivot shows data like so per default (tabular view, without subtotals).

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		Category	Priority	Task	Sub Task		Category	Priority	Task	Sub Task
2		House	1	Clean	Sweep Floors		Garden	1	Plant Trees	fertilise
3		House	1	Clean	Wash Dishes					plant fruit trees
4		House	2	Install bench	assemble items					Plant natives
5		House	2	Install bench	screw bench top on					water
6		House	2	Install bench	seal benchtop to wall		House	1	Clean	Sweep Floors
7		Garden	1	Plant Trees	Plant natives					Wash Dishes
8		Garden	1	Plant Trees	plant fruit trees			2	Install bench	assemble items
9		Garden	1	Plant Trees	fertilise					screw bench top on
10		Garden	1	Plant Trees	water					seal benchtop to wall
11
Sheet1

Cell Formulas
Range	Formula
B3:D3,B8:D10,B5:D6	B3	=B2

EDIT 2: The "first field" in the "rows" section of the picot, determines which labels are repeated.

Book1
	G	H	I	J
1	Task	Category	Priority	Sub Task
2	Clean	House	1	Sweep Floors
3				Wash Dishes
4	Install bench	House	2	assemble items
5				screw bench top on
6				seal benchtop to wall
7	Plant Trees	Garden	1	fertilise
8				plant fruit trees
9				Plant natives
10				water
Sheet1

I had a Play with Pivot Tables but cant get it to display how I wanted.
Ideally id like to be able to just delete the data when the task is done but with a pivot table I would have to open the data sheet and delete the item then refresh the pivot table correct?

 

[B-Man]

As a general tip, it is very helpful if your account details tell us a bit more on the version you use and your operating system. Also sharing data with XL2BB add-on is better then plane pasted data.

Maybe Power Query can offer you a solution, avoiding to type in data manually, as it has a feature called "Fill Down".

let
    Source = Excel.Workbook(File.Contents("G:\Uploads\TaskSubTaskFilter.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category", type text}, {"Priority", Int64.Type}, {"Task/Subtask", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "SubTask", each if [Category] = null then [#"Task/Subtask"] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Task", each if [SubTask] = null then [#"Task/Subtask"] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Category", "Priority", "Task"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([SubTask] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Task/Subtask"})
in
    #"Removed Columns"

I made a quick tutorial, but the animated JPEG is too big to share. Maybe I'll share it later if I can edit it.

I did notice the XL2BB thing but not sure how to use it.
I'm using windows 10, Excel 2013

A quick tutorial would be great. while I dabble in excel there's a lot of stuff I haven't touched.

is this a macro to prefill the data after i have typed or during my typing?
is there a way to not display the duplicate info?

 

[GraH]

I did notice the XL2BB thing but not sure how to use it.
I'm using windows 10, Excel 2013

Have a read on XL2BB
Thanks for letting us know. Please do put this info on your account details. It will be useful for your later posts.

A quick tutorial would be great. while I dabble in excel there's a lot of stuff I haven't touched.

Seems I can't shrink the size of the animated gif I made As I'm back to work after some holidays, I won't have time to make a written version of it.

is this a macro to prefill the data after i have typed or during my typing?

No, Power Query needs to be "refreshed", but if you have it loaded as a pivot, a single refresh is enough (the Pivot will refresh the query).

is there a way to not display the duplicate info?

The duplicated information depends on how you use the fields inside the pivot, as shown in #2.

Ideally id like to be able to just delete the data when the task is done but with a pivot table I would have to open the data sheet and delete the item then refresh the pivot table correct?

My suggestion would be to use a status field then use this field in a filter of the pivot. But, yes, updates are done inside the raw data.