Formula to pick up Dept and Sub Dept

[Hazeyt]

Good afternoon. I am hoping someone can help with my question below thanks.

I have a dataset with Department and Sub Department headings that I layer on a monthly basis and these headings can change on a monthly basis along with new Sub Departments.

At present I use a pivot table and copy into a separate tab to capture them all.

Rather than doing a pivot table, I am looking for a formula to pick up the Department name first then list the Sub Departments which would include all old and new for each month from the dataset.

Thanks
Hazel

 

[awoohaw]

What do you mean by "layer"?
Can you post a small sample of your data and what your expected results would look like? Use the xl2bb link above or in the signature below.

 

[Hazeyt]

I have columns with Dept and Sub Depts headings and a column stating which month.

Every month I get a new list which I add to the bottom of my dataset (layer) and add the next month in the month column.

The list is for the financial year.

Hope this makes sense. Thanks

 

[awoohaw]

If this isn't what you want, then you really need to provide some examples.
But, try this:

Book1
	A	B	C	D	E	F	G
1	Dept	SubDept	Month		Dept	SubDept	Month
2	Dept1	SubDpt2	Jan		Dept1	SubDpt2	Jan
3	Dept2	SubDpt1	Feb		Dept2	SubDpt1	Jan
4	Dept3	SubDpt2	Mar		Dept3	SubDpt2	Jan
5	Dept4	SubDpt1	Jan		Dept4	SubDpt1	Jan
6	Dept1	SubDpt2	Feb		Dept1	SubDpt2	Feb
7	Dept2	SubDpt1	Mar		Dept2	SubDpt1	Feb
8	Dept3	SubDpt2	Jan		Dept3	SubDpt2	Feb
9	Dept4	SubDpt1	Feb		Dept4	SubDpt1	Feb
10	Dept1	SubDpt2	Mar		Dept1	SubDpt2	Mar
11	Dept2	SubDpt1	Jan		Dept2	SubDpt1	Mar
12	Dept3	SubDpt2	Feb		Dept3	SubDpt2	Mar
13	Dept4	SubDpt1	Mar		Dept4	SubDpt1	Mar
14	Dept1	SubDpt2	Jan
15	Dept2	SubDpt1	Feb
16	Dept3	SubDpt2	Mar
17	Dept4	SubDpt1	Jan
18	Dept1	SubDpt2	Feb
19	Dept2	SubDpt1	Mar
20	Dept3	SubDpt2	Jan
21	Dept4	SubDpt1	Feb
22	Dept1	SubDpt2	Mar
23	Dept2	SubDpt1	Jan
24	Dept3	SubDpt2	Feb
25	Dept4	SubDpt1	Mar
26	Dept1	SubDpt2	Jan
27	Dept2	SubDpt1	Feb
Sheet1

Cell Formulas
Range	Formula
E2:G13	E2	=SORT(SORT(SORT(UNIQUE(A2:C26,FALSE,FALSE),2,1,FALSE),1,1,FALSE),3,1,FALSE)
Dynamic array formulas.

 

[Hazeyt]

If this isn't what you want, then you really need to provide some examples.
But, try this:

Book1
	A	B	C	D	E	F	G
1	Dept	SubDept	Month		Dept	SubDept	Month
2	Dept1	SubDpt2	Jan		Dept1	SubDpt2	Jan
3	Dept2	SubDpt1	Feb		Dept2	SubDpt1	Jan
4	Dept3	SubDpt2	Mar		Dept3	SubDpt2	Jan
5	Dept4	SubDpt1	Jan		Dept4	SubDpt1	Jan
6	Dept1	SubDpt2	Feb		Dept1	SubDpt2	Feb
7	Dept2	SubDpt1	Mar		Dept2	SubDpt1	Feb
8	Dept3	SubDpt2	Jan		Dept3	SubDpt2	Feb
9	Dept4	SubDpt1	Feb		Dept4	SubDpt1	Feb
10	Dept1	SubDpt2	Mar		Dept1	SubDpt2	Mar
11	Dept2	SubDpt1	Jan		Dept2	SubDpt1	Mar
12	Dept3	SubDpt2	Feb		Dept3	SubDpt2	Mar
13	Dept4	SubDpt1	Mar		Dept4	SubDpt1	Mar
14	Dept1	SubDpt2	Jan
15	Dept2	SubDpt1	Feb
16	Dept3	SubDpt2	Mar
17	Dept4	SubDpt1	Jan
18	Dept1	SubDpt2	Feb
19	Dept2	SubDpt1	Mar
20	Dept3	SubDpt2	Jan
21	Dept4	SubDpt1	Feb
22	Dept1	SubDpt2	Mar
23	Dept2	SubDpt1	Jan
24	Dept3	SubDpt2	Feb
25	Dept4	SubDpt1	Mar
26	Dept1	SubDpt2	Jan
27	Dept2	SubDpt1	Feb
Sheet1

Cell Formulas
Range	Formula
E2:G13	E2	=SORT(SORT(SORT(UNIQUE(A2:C26,FALSE,FALSE),2,1,FALSE),1,1,FALSE),3,1,FALSE)
Dynamic array formulas.

Thank you for your help.

 

[awoohaw]

My pleasure, happy to help. Best Wishes.