Formulae to create dynamic list from table

[Scotster]

I'm fumbling around in the dark with this one as I can't get anything in play here that isn't a circular reference (Maybe that's the only solution though).

So what I want to be able to do is create a dynamic list of parts based on their appearances in a table that aren't 0. The table looks as follows:

PartNo	Wk1	Wk2	Wk3	Wk4	Wk5	Wk6	Wk7	Wk8	Wk9
PartX	0​	0​	0​	0​	0​	0​	0​	0​	0​
Part1	0​	5​	0​	15​	0​	1000​	0​	0​	0​
Part2	10​	0​	10​	0​	10​	0​	10​	0​	10​
Part3	0​	10​	0​	10​	0​	10​	0​	10​	0​
Part4	0​	0​	0​	0​	1000​	0​	0​	0​	0​

From here, what I would like is a dynamic list depending on the values in the table. With PartX not having any quantities against it, it won't appear in the list at all. The list will look as follows:

Part No

Part1

Part1

Part1

Part2

Part2

Part2

Part2

Part2

Part3

Part3

Part3

Part3

Part4

Is there a way of dynamically creating the list above without using VBA? I don't mind using a VBA array if that's the solution, but I would rather do it with a formula if I could.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	PartNo	Wk1	Wk2	Wk3	Wk4	Wk5	Wk6	Wk7	Wk8	Wk9
2	PartX	0	0	0	0	0	0	0	0	0
3	Part1	0	5	0	15	0	1000	0	0	0
4	Part2	10	0	10	0	10	0	10	0	10
5	Part3	0	10	0	10	0	10	0	10	0
6	Part4	0	0	0	0	1000	0	0	0	0
7
8
9	Part1
10	Part1
11	Part1
12	Part2
13	Part2
14	Part2
15	Part2
16	Part2
17	Part3
18	Part3
19	Part3
20	Part3
21	Part4
22
Main

Cell Formulas
Range	Formula
A9:A21	A9	=TOCOL(IF(B2:J6>0,A2:A6,x),3)
Dynamic array formulas.

 

[Scotster]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	PartNo	Wk1	Wk2	Wk3	Wk4	Wk5	Wk6	Wk7	Wk8	Wk9
2	PartX	0	0	0	0	0	0	0	0	0
3	Part1	0	5	0	15	0	1000	0	0	0
4	Part2	10	0	10	0	10	0	10	0	10
5	Part3	0	10	0	10	0	10	0	10	0
6	Part4	0	0	0	0	1000	0	0	0	0
7
8
9	Part1
10	Part1
11	Part1
12	Part2
13	Part2
14	Part2
15	Part2
16	Part2
17	Part3
18	Part3
19	Part3
20	Part3
21	Part4
22
Main

Cell Formulas
Range	Formula
A9:A21	A9	=TOCOL(IF(B2:J6>0,A2:A6,x),3)
Dynamic array formulas.

That would be perfect but unfortunately I only have access to 2016 on my works PC. I think ToCol is a 365 addition?

 

[Fluff]

In future it would be helpful if you mentioned that, or change your profile to just show 2016.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	PartNo	Wk1	Wk2	Wk3	Wk4	Wk5	Wk6	Wk7	Wk8	Wk9
2	PartX	0	0	0	0	0	0	0	0	0
3	Part1	0	5	0	15	0	1000	0	0	0
4	Part2	10	0	10	0	10	0	10	0	10
5	Part3	0	10	0	10	0	10	0	10	0
6	Part4	0	0	0	0	1000	0	0	0	0
7
8
9	Part1
10	Part1
11	Part1
12	Part2
13	Part2
14	Part2
15	Part2
16	Part2
17	Part3
18	Part3
19	Part3
20	Part3
21	Part4
22
23
24
25
26
Main

Cell Formulas
Range	Formula
A9:A24	A9	=IFERROR(INDEX($A$2:$A$6,AGGREGATE(15,6,(ROW($A$2:$A$6)-ROW($A$2)+1)/($B$2:$J$6<>0),ROWS(A$9:A9))),"")