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

brandtd

New Member
Joined
Aug 29, 2022
Messages
4
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
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.
masterlistsublist.jpg


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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Welcome to the MrExcel forum!

Try:

Book1
ABCDEF
1Sub List NameSub List BarcodeMaster List NameMaster List Barcode# of Items
2Widget999900000001Widget99990000000112
3Widget999900000001Trinket9999000000026
4Widget999900000001Object99990000000312
5Widget999900000001Thing9999000000040
6Widget999900000001
7Widget999900000001
8Widget999900000001
9Widget999900000001
10Widget999900000001
11Widget999900000001
12Widget999900000001
13Widget999900000001
14Trinket999900000002
15Trinket999900000002
16Trinket999900000002
17Trinket999900000002
18Trinket999900000002
19Trinket999900000002
20Object999900000003
21Object999900000003
22Object999900000003
23Object999900000003
24Object999900000003
25Object999900000003
26Object999900000003
27Object999900000003
28Object999900000003
29Object999900000003
30Object999900000003
31Object999900000003
32
Sheet1
Cell Formulas
RangeFormula
A2:B31A2=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.
 
Upvote 0
Welcome to the MrExcel forum!

Try:

Book1
ABCDEF
1Sub List NameSub List BarcodeMaster List NameMaster List Barcode# of Items
2Widget999900000001Widget99990000000112
3Widget999900000001Trinket9999000000026
4Widget999900000001Object99990000000312
5Widget999900000001Thing9999000000040
6Widget999900000001
7Widget999900000001
8Widget999900000001
9Widget999900000001
10Widget999900000001
11Widget999900000001
12Widget999900000001
13Widget999900000001
14Trinket999900000002
15Trinket999900000002
16Trinket999900000002
17Trinket999900000002
18Trinket999900000002
19Trinket999900000002
20Object999900000003
21Object999900000003
22Object999900000003
23Object999900000003
24Object999900000003
25Object999900000003
26Object999900000003
27Object999900000003
28Object999900000003
29Object999900000003
30Object999900000003
31Object999900000003
32
Sheet1
Cell Formulas
RangeFormula
A2:B31A2=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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
OK, this version should work in 2016, but you'll have to drag the formulas down:

Book1
ABCDEF
1Sub List NameSub List BarcodeMaster List NameMaster List Barcode# of Items
2Widget999900000001Widget9999000000012
3Widget999900000001Trinket9999000000020
4Object999900000003Object9999000000033
5Object999900000003Thing9999000000044
6Object999900000003
7Thing999900000004
8Thing999900000004
9Thing999900000004
10Thing999900000004
11  
Sheet3
Cell Formulas
RangeFormula
A2:B11A2=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.
 
Upvote 0
OK, this version should work in 2016, but you'll have to drag the formulas down:

Book1
ABCDEF
1Sub List NameSub List BarcodeMaster List NameMaster List Barcode# of Items
2Widget999900000001Widget9999000000012
3Widget999900000001Trinket9999000000020
4Object999900000003Object9999000000033
5Object999900000003Thing9999000000044
6Object999900000003
7Thing999900000004
8Thing999900000004
9Thing999900000004
10Thing999900000004
11  
Sheet3
Cell Formulas
RangeFormula
A2:B11A2=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!
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

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