creating a dynamic list based on values in a table

[rwells]

I am attempting to create one entry for each instance of the items listed in a table (all in one column) so the list of entries can be imported into another program. The number of entries required for each item is equal to the value in it's corresponding cell. Naming convention for the output entry should be: Y-axis label_X-axis label_sequence # These values are dynamic. The number of entries in the output column need to be able to change based on the value in each cell. Sample data attached. I'm using Office 365, and am open to either a formula or VBA. Thanks in advance for any help!

 

[Cubist]

For formula, try:

Book1 (version 2).xlsb
	A	B	C	D	E	F
1
2		Apples	Bananas	Pears
3	North	1	3	2		North_Apples_1
4	South	0	2	2		North_Bananas_1
5	East	1	0	3		North_Bananas_2
6	West	2	1	1		North_Bananas_3
7						North_Pears_1
8			total	18		North_Pears_2
9						South_Bananas_1
10						South_Bananas_2
11						South_Pears_1
12						South_Pears_2
13						East_Apples_1
14						East_Pears_1
15						East_Pears_2
16						East_Pears_3
17						West_Apples_1
18						West_Apples_2
19						West_Bananas_1
20						West_Pears_1
Sheet6

Cell Formulas
Range	Formula
F3:F20	F3	=LET( val,TOCOL(IFS(B3:D6>0,B3:D6),2), coor,TOCOL(IFS(B3:D6>0,A3:A6 &"_"&B2:D2),2), TOCOL(IFS(val>=SEQUENCE(,MAX(val)),coor),2) & "_" & DROP(REDUCE("",val,LAMBDA(a,b,VSTACK(a,SEQUENCE(b)))),1))
D8	D8	=SUM(B3:D6)
Dynamic array formulas.

 

[Fluff]

I'm using Office 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[rwells]

For formula, try:

Book1 (version 2).xlsb
	A	B	C	D	E	F
1
2		Apples	Bananas	Pears
3	North	1	3	2		North_Apples_1
4	South	0	2	2		North_Bananas_1
5	East	1	0	3		North_Bananas_2
6	West	2	1	1		North_Bananas_3
7						North_Pears_1
8			total	18		North_Pears_2
9						South_Bananas_1
10						South_Bananas_2
11						South_Pears_1
12						South_Pears_2
13						East_Apples_1
14						East_Pears_1
15						East_Pears_2
16						East_Pears_3
17						West_Apples_1
18						West_Apples_2
19						West_Bananas_1
20						West_Pears_1
Sheet6

Cell Formulas
Range	Formula
F3:F20	F3	=LET( val,TOCOL(IFS(B3:D6>0,B3:D6),2), coor,TOCOL(IFS(B3:D6>0,A3:A6 &"_"&B2:D2),2), TOCOL(IFS(val>=SEQUENCE(,MAX(val)),coor),2) & "_" & DROP(REDUCE("",val,LAMBDA(a,b,VSTACK(a,SEQUENCE(b)))),1))
D8	D8	=SUM(B3:D6)
Dynamic array formulas.

Genius! Thank you very much!