List based on part name and QTY

daiv07

New Member
Joined
Aug 31, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Web
I have a parts list that includes multiple fields of information and I am hoping to auto generate a list I can use to make parts labels. The workbook has multiple sheets, and among the information, parts may have multiples for the QTY field. How can I pull out the QTY and Part Number field, generate the appropriate amount of entries and move to the next part and do the same on all parts and sheets, etc. in order to have a complete list of entries to make an appropriate amount of labels in Word.

For example:
Label: Part 1 QTY: 30
Label: Part 2 QTY: 5

I would need to generate 30 individual labels for part one and 5 for part 2.

Thanks.
David.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
544
Office Version
  1. 365
Platform
  1. Windows
Are the Part and Quantity in the same cell or cells next to each other?
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
like this ?

LabelQtyLabelQty
Part 130Part 130
Part 25Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 130
Part 25
Part 25
Part 25
Part 25
Part 25


or like this

LabelQtyLabelsQty
Part 130Part 1-130
Part 25Part 1-230
Part 1-330
Part 1-430
Part 1-530
Part 1-630
Part 1-730
Part 1-830
Part 1-930
Part 1-1030
Part 1-1130
Part 1-1230
Part 1-1330
Part 1-1430
Part 1-1530
Part 1-1630
Part 1-1730
Part 1-1830
Part 1-1930
Part 1-2030
Part 1-2130
Part 1-2230
Part 1-2330
Part 1-2430
Part 1-2530
Part 1-2630
Part 1-2730
Part 1-2830
Part 1-2930
Part 1-3030
Part 2-15
Part 2-25
Part 2-35
Part 2-45
Part 2-55
 

daiv07

New Member
Joined
Aug 31, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Web
Yes! Option A where the part number stays the same every time.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

this is M for Power Query
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    List = Table.AddColumn(Source, "Custom", each {1..[Qty]}),
    Expand = Table.ExpandListColumn(List, "Custom"),
    TSC = Table.SelectColumns(Expand,{"Label", "Qty"})
in
    TSC
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,020
Office Version
  1. 365
Platform
  1. Windows
Hey @sandy666 , just playing around with the wild functional programming side of PQ and came up with this. It does the same thing and is a lot more confusing and probably slower... BUT, it was a fun exercise.

Book1 (Autosaved).xlsx
ABCDE
1LabelQtyLabelQty
2Part 130Part 130
3Part 25Part 130
4Part 310Part 130
5Part 130
6Part 130
7Part 130
8Part 130
9Part 130
10Part 130
11Part 130
12Part 130
13Part 130
14Part 130
15Part 130
16Part 130
17Part 130
18Part 130
19Part 130
20Part 130
21Part 130
22Part 130
23Part 130
24Part 130
25Part 130
26Part 130
27Part 130
28Part 130
29Part 130
30Part 130
31Part 130
32Part 25
33Part 25
34Part 25
35Part 25
36Part 25
37Part 310
38Part 310
39Part 310
40Part 310
41Part 310
42Part 310
43Part 310
44Part 310
45Part 310
46Part 310
Sheet4


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    Total = List.Sum(Source[Qty]),
    LG = Table.FromRecords(List.Generate(
        ()=> [Counter = 1, i=1, q=Source{0}[Qty], id = 0, rec = Source{0}],
        each [Counter]< Total+1,
        each [
            Counter=[Counter]+1, 
            i = if [i] = [q]-1 then 0 else [i] + 1, 
            q = Source{[id]}[Qty], 
            id = if [q] = [i]+1 then [id] + 1 else [id],
            rec =Source{[id]}
            ],
        each [rec]
    ))
in
    LG
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

@lrobbo314
really fun :)
I see you test List.Generate hard but imho if I don't need to use it, I don't use it
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,020
Office Version
  1. 365
Platform
  1. Windows
Yeah, I've read that it should be used as a last resort because it isn't the best for performance.

BTW, very clever solution you had on this one! The first time I took a stab at it I used like 2X as many functions as you did.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
@lrobbo314
:biggrin: Thanks
I'm trying to use functions that a normal user can understand (from the ribbon or simple ones from the #shared list)
{1 .. [Qty]} is not that case, but it's simple as hell :ROFLMAO:
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
Power Query is Owesome.
But if you want Formula based solution you can try this also
'
Book1
ABCDEFGHI
3Label PartQuantityLabelQuantity
4Part130Part130
5Part 25Part130
6Part130
7Part130
8Part130
9Part130
10Part130
11Part130
12Part130
13Part130
14Part130
15Part130
16Part130
17Part130
18Part130
19Part130
20Part130
21Part130
22Part130
23Part130
24Part130
25Part130
26Part130
27Part130
28Part130
29Part130
30Part130
31Part130
32Part130
33Part130
34Part 25
35Part 25
36Part 25
37Part 25
38Part 25
39
Sheet1
Cell Formulas
RangeFormula
G4:G38G4=LOOKUP(ROW(INDIRECT("1:"&SUM($C$4:$C$5))),IFERROR(SUBTOTAL(9,OFFSET($C$4,,,ROW($C$4:$C$6)-ROW($C$4))),0)+1,T(OFFSET($B$4,ROW($C$4:$C$6)-ROW($C$4),)))
H4:H38H4=LOOKUP(ROW(INDIRECT("1:"&SUM($C$4:$C$5))),IFERROR(SUBTOTAL(9,OFFSET($C$4,,,ROW($C$4:$C$6)-ROW($C$4))),0)+1,IFERROR(SUBTOTAL(9,OFFSET($C$4,ROW($C$4:$C$6)-ROW($C$4),)),C4))
Dynamic array formulas.


With Control+Shift+Enter and Drag it down
 

Watch MrExcel Video

Forum statistics

Threads
1,122,298
Messages
5,595,310
Members
413,986
Latest member
Elizsk

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
Top