Create a sub list from a master list with multiples based on the quantity that I choose

[brandtd]

So I will have a master list of 500+ items. In my example, my master list has Widget, Trinket, Object, etc. I'd like to create a sub list in columns A and B to take items from columns D and E but ONLY if there's a quantity in column F. I would like the number of items in the sublist for each item to reflect the quantity that I have in column F.

So in my image example, I have the number 12 in the # of Items columns because I want 12 instaces of Widgets to appear in the sub list with the correct barcode associated with Widget. Then, I want 6 instances Trinkets to appear with its respective barcode and 12 instances of Objects with its barcode. There's no quantity in column F for Thing, so Thing shouldn't appear in the sub list at all.

If were to change the quantity from 12 to 9 in my master list for Widget, then Widget should only appear 9 times in the sub list, not 12.

I hope this all makes sense! Thanks in advance for any help!

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E	F
1	Sub List Name	Sub List Barcode		Master List Name	Master List Barcode	# of Items
2	Widget	999900000001		Widget	999900000001	12
3	Widget	999900000001		Trinket	999900000002	6
4	Widget	999900000001		Object	999900000003	12
5	Widget	999900000001		Thing	999900000004	0
6	Widget	999900000001
7	Widget	999900000001
8	Widget	999900000001
9	Widget	999900000001
10	Widget	999900000001
11	Widget	999900000001
12	Widget	999900000001
13	Widget	999900000001
14	Trinket	999900000002
15	Trinket	999900000002
16	Trinket	999900000002
17	Trinket	999900000002
18	Trinket	999900000002
19	Trinket	999900000002
20	Object	999900000003
21	Object	999900000003
22	Object	999900000003
23	Object	999900000003
24	Object	999900000003
25	Object	999900000003
26	Object	999900000003
27	Object	999900000003
28	Object	999900000003
29	Object	999900000003
30	Object	999900000003
31	Object	999900000003
32
Sheet1

Cell Formulas
Range	Formula
A2:B31	A2	=LET(s,MATCH(SEQUENCE(SUM(F2:F5),,0),SUBTOTAL(9,OFFSET(F1,0,0,SEQUENCE(COUNT(F2:F5))))),CHOOSE({1,2},INDEX(D2:D5,s),INDEX(E2:E5,s)))
Dynamic array formulas.

 

[brandtd]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E	F
1	Sub List Name	Sub List Barcode		Master List Name	Master List Barcode	# of Items
2	Widget	999900000001		Widget	999900000001	12
3	Widget	999900000001		Trinket	999900000002	6
4	Widget	999900000001		Object	999900000003	12
5	Widget	999900000001		Thing	999900000004	0
6	Widget	999900000001
7	Widget	999900000001
8	Widget	999900000001
9	Widget	999900000001
10	Widget	999900000001
11	Widget	999900000001
12	Widget	999900000001
13	Widget	999900000001
14	Trinket	999900000002
15	Trinket	999900000002
16	Trinket	999900000002
17	Trinket	999900000002
18	Trinket	999900000002
19	Trinket	999900000002
20	Object	999900000003
21	Object	999900000003
22	Object	999900000003
23	Object	999900000003
24	Object	999900000003
25	Object	999900000003
26	Object	999900000003
27	Object	999900000003
28	Object	999900000003
29	Object	999900000003
30	Object	999900000003
31	Object	999900000003
32
Sheet1

Cell Formulas
Range	Formula
A2:B31	A2	=LET(s,MATCH(SEQUENCE(SUM(F2:F5),,0),SUBTOTAL(9,OFFSET(F1,0,0,SEQUENCE(COUNT(F2:F5))))),CHOOSE({1,2},INDEX(D2:D5,s),INDEX(E2:E5,s)))
Dynamic array formulas.

Thanks for the reply! When I paste that formula in A2, I get #NAME as the output.

 

[Eric W]

Which version of Excel are you using? That formula should work in 365 and 2021, but it won't in 2019. If that's the one you're using, then I'll have to adapt it.

 

[brandtd]

Which version of Excel are you using? That formula should work in 365 and 2021, but it won't in 2019. If that's the one you're using, then I'll have to adapt it.

Oh, I was way off. This laptop has Excel 2016. No wonder why!

 

[Eric W]

OK, this version should work in 2016, but you'll have to drag the formulas down:

Book1
	A	B	C	D	E	F
1	Sub List Name	Sub List Barcode		Master List Name	Master List Barcode	# of Items
2	Widget	999900000001		Widget	999900000001	2
3	Widget	999900000001		Trinket	999900000002	0
4	Object	999900000003		Object	999900000003	3
5	Object	999900000003		Thing	999900000004	4
6	Object	999900000003
7	Thing	999900000004
8	Thing	999900000004
9	Thing	999900000004
10	Thing	999900000004
11
Sheet3

Cell Formulas
Range	Formula
A2:B11	A2	=IF(ROWS(A$2:A2)>SUM($F$2:$F$10),"",INDEX(D$2:D$10,MATCH(ROWS(A$2:A2)-1,SUBTOTAL(9,OFFSET($F$1,0,0,ROW(INDIRECT("1:"&COUNT($F$2:$F$10))))))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[brandtd]

OK, this version should work in 2016, but you'll have to drag the formulas down:

Book1
	A	B	C	D	E	F
1	Sub List Name	Sub List Barcode		Master List Name	Master List Barcode	# of Items
2	Widget	999900000001		Widget	999900000001	2
3	Widget	999900000001		Trinket	999900000002	0
4	Object	999900000003		Object	999900000003	3
5	Object	999900000003		Thing	999900000004	4
6	Object	999900000003
7	Thing	999900000004
8	Thing	999900000004
9	Thing	999900000004
10	Thing	999900000004
11
Sheet3

Cell Formulas
Range	Formula
A2:B11	A2	=IF(ROWS(A$2:A2)>SUM($F$2:$F$10),"",INDEX(D$2:D$10,MATCH(ROWS(A$2:A2)-1,SUBTOTAL(9,OFFSET($F$1,0,0,ROW(INDIRECT("1:"&COUNT($F$2:$F$10))))))))
Press CTRL+SHIFT+ENTER to enter array formulas.

I moved everything over to my other laptop with 365 and used your first example and it works great, thanks!

 

[Eric W]

Glad I could help! Thanks for the feedback.