Formulae to create dynamic list from table

Scotster

Board Regular
Joined
May 29, 2017
Messages
59
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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:

PartNoWk1Wk2Wk3Wk4Wk5Wk6Wk7Wk8Wk9
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
How about
Fluff.xlsm
ABCDEFGHIJ
1PartNoWk1Wk2Wk3Wk4Wk5Wk6Wk7Wk8Wk9
2PartX000000000
3Part10501501000000
4Part210010010010010
5Part30100100100100
6Part4000010000000
7
8
9Part1
10Part1
11Part1
12Part2
13Part2
14Part2
15Part2
16Part2
17Part3
18Part3
19Part3
20Part3
21Part4
22
Main
Cell Formulas
RangeFormula
A9:A21A9=TOCOL(IF(B2:J6>0,A2:A6,x),3)
Dynamic array formulas.
 
Upvote 0
How about
Fluff.xlsm
ABCDEFGHIJ
1PartNoWk1Wk2Wk3Wk4Wk5Wk6Wk7Wk8Wk9
2PartX000000000
3Part10501501000000
4Part210010010010010
5Part30100100100100
6Part4000010000000
7
8
9Part1
10Part1
11Part1
12Part2
13Part2
14Part2
15Part2
16Part2
17Part3
18Part3
19Part3
20Part3
21Part4
22
Main
Cell Formulas
RangeFormula
A9:A21A9=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?
 
Upvote 0
In future it would be helpful if you mentioned that, or change your profile to just show 2016. ;)
 
Upvote 0
How about
Fluff.xlsm
ABCDEFGHIJ
1PartNoWk1Wk2Wk3Wk4Wk5Wk6Wk7Wk8Wk9
2PartX000000000
3Part10501501000000
4Part210010010010010
5Part30100100100100
6Part4000010000000
7
8
9Part1
10Part1
11Part1
12Part2
13Part2
14Part2
15Part2
16Part2
17Part3
18Part3
19Part3
20Part3
21Part4
22 
23 
24 
25
26
Main
Cell Formulas
RangeFormula
A9:A24A9=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))),"")
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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