making a list out of a list with quantities

Taco G

New Member
Joined
Sep 7, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Hello,

I have a problem and I am hoping someone can help me. I have an excellist with some materials and quantities next to it. Now I need a list that if the quantity is two this material will be included as two lines in the list. I need this for printing labels. I have been cracking my brain but can't figure it out so I was hoping someone here could help me.

Thanks in advance!

Best regards,
Taco
MAKT.MATNR
Material Number
MAKT.MAKTX
Material Description (Short Text)
MARD.LABST
Valuated Unrestricted-Use Stock
New list
R600201000EDC Outlet Cast Y piece Assembly
1,00​
EDC Outlet Cast Y piece Assembly
R600201010Cast Return Bend + Thermow. Fitting Arr.
3,00​
Cast Return Bend + Thermow. Fitting Arr.
R600201020Cast Return Bend Without Thermow. Fittin
3,00​
Cast Return Bend + Thermow. Fitting Arr.
R600201030Impeller (GAD 3118C-19003-01 pos 14)
1,00​
Cast Return Bend + Thermow. Fitting Arr.
R600201040Shaft short(GAD 3118C-19003-01 pos 21)
1,00​
Cast Return Bend Without Thermow. Fittin
R600201060Impeller (GAD 311BB-19003-01 pos 14)
1,00​
Cast Return Bend Without Thermow. Fittin
R600201070Shaft long(GAD 311BB-19003-01 pos 21)
1,00​
Cast Return Bend Without Thermow. Fittin
R600201080Cast Pin R201A/B (1613FHA-9)
2,00​
Impeller (GAD 3118C-19003-01 pos 14)
R600201090Cast Pin R201A/B (1613FHA-10)
2,00​
Shaft short(GAD 3118C-19003-01 pos 21)
R600201100Cast Pin R201A/B (1613FHA-11)
8,00​
Impeller (GAD 311BB-19003-01 pos 14)
R600201130Tub Support R201C (1249T/01-07)
10,00​
Shaft long(GAD 311BB-19003-01 pos 21)
R600201110Cast Pin R201D (1623FHA-16)
6,00​
Cast Pin R201A/B (1613FHA-9)
R600201120Washer R201D (1623FHA-20)
6,00​
Cast Pin R201A/B (1613FHA-9)
R636815110Inducer Dickow NCLih 100/250 P0452B/C
1,00​
etc etc….
R636815100Waaier Dickow NCLih 100/250 P0452B/C
1,00​
R695731500Regelkl. HW9000 DN40 600Lbs RF STD 1.061
1,00​
R688650300Revisieset tbv Adams terugsl. DN350/PN10
1,00​
R030510000Pijp CuNi70/30 1" x 2.11mm x 6500mm
520,00​
R910891010Pressure controller FRS-520 10-30mBarg
2,00​
R060102050Pakking Gore-Tex DN50 PN16 GN501
0,00​
R695741000Groth drukveiligheid 6" 150lbs CS/PTFE
2,00​
R695732250Sprinklerkl. GEFA/NORBRO DN100/PN16
16,00​
R733501000Regelkl. CAMFLEX II 2" 150# Wafer CV53
0,00​
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,748
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Are you looking for a formula solution, or macro, or power query?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,748
Office Version
  1. 365
Platform
  1. Windows
Here is a macro solution.

VBA Code:
Sub ExpandList()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("B2", Range("C" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To Rows.Count, 1 To 1)
  For i = 1 To UBound(a)
    For j = 1 To a(i, 2)
      k = k + 1
      b(k, 1) = a(i, 1)
    Next j
  Next i
  Range("E2").Resize(k).Value = b
End Sub

Taco G.xlsm
ABCDE
1MAKT.MATNRMAKT.MAKTXMARD.LABSTNew list
2R600201000EDC Outlet Cast Y piece Assembly1.00EDC Outlet Cast Y piece Assembly
3R600201010Cast Return Bend + Thermow. Fitting Arr.3.00Cast Return Bend + Thermow. Fitting Arr.
4R600201020Cast Return Bend Without Thermow. Fittin3.00Cast Return Bend + Thermow. Fitting Arr.
5R600201030Impeller (GAD 3118C-19003-01 pos 14)1.00Cast Return Bend + Thermow. Fitting Arr.
6R600201040Shaft short(GAD 3118C-19003-01 pos 21)1.00Cast Return Bend Without Thermow. Fittin
7R600201060Impeller (GAD 311BB-19003-01 pos 14)1.00Cast Return Bend Without Thermow. Fittin
8R600201070Shaft long(GAD 311BB-19003-01 pos 21)1.00Cast Return Bend Without Thermow. Fittin
9R600201080Cast Pin R201A/B (1613FHA-9)2.00Impeller (GAD 3118C-19003-01 pos 14)
10R600201090Cast Pin R201A/B (1613FHA-10)2.00Shaft short(GAD 3118C-19003-01 pos 21)
11R600201100Cast Pin R201A/B (1613FHA-11)8.00Impeller (GAD 311BB-19003-01 pos 14)
12R600201130Tub Support R201C (1249T/01-07)10.00Shaft long(GAD 311BB-19003-01 pos 21)
13R600201110Cast Pin R201D (1623FHA-16)6.00Cast Pin R201A/B (1613FHA-9)
14R600201120Washer R201D (1623FHA-20)6.00Cast Pin R201A/B (1613FHA-9)
15R636815110Inducer Dickow NCLih 100/250 P0452B/C1.00Cast Pin R201A/B (1613FHA-10)
16R636815100Waaier Dickow NCLih 100/250 P0452B/C1.00Cast Pin R201A/B (1613FHA-10)
17R695731500Regelkl. HW9000 DN40 600Lbs RF STD 1.0611.00Cast Pin R201A/B (1613FHA-11)
18R688650300Revisieset tbv Adams terugsl. DN350/PN101.00Cast Pin R201A/B (1613FHA-11)
19R030510000Pijp CuNi70/30 1" x 2.11mm x 6500mm520.00Cast Pin R201A/B (1613FHA-11)
20R910891010Pressure controller FRS-520 10-30mBarg2.00Cast Pin R201A/B (1613FHA-11)
21R060102050Pakking Gore-Tex DN50 PN16 GN5010.00Cast Pin R201A/B (1613FHA-11)
22R695741000Groth drukveiligheid 6" 150lbs CS/PTFE2.00Cast Pin R201A/B (1613FHA-11)
23R695732250Sprinklerkl. GEFA/NORBRO DN100/PN1616.00Cast Pin R201A/B (1613FHA-11)
24R733501000Regelkl. CAMFLEX II 2" 150# Wafer CV530.00Cast Pin R201A/B (1613FHA-11)
25Tub Support R201C (1249T/01-07)
26Tub Support R201C (1249T/01-07)
27Tub Support R201C (1249T/01-07)
28Tub Support R201C (1249T/01-07)
29Tub Support R201C (1249T/01-07)
30Tub Support R201C (1249T/01-07)
31Tub Support R201C (1249T/01-07)
32Tub Support R201C (1249T/01-07)
33Tub Support R201C (1249T/01-07)
34Tub Support R201C (1249T/01-07)
35Cast Pin R201D (1623FHA-16)
Sheet1
 
Solution

Taco G

New Member
Joined
Sep 7, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Dear Peter,

Thank you so much for your solution. I don't much experience with marco's, but I copied the macro into the workam not that into macro's, but I copied your marco and run it and it worked! I don't really understand it, but so good that it works, again much thanks!

You were asking me if I was looking for a formula solution. Is there any? Just to sooth my mind and try to learn something on the formula's to use (macro's is one bridge to far for me I think );)

best regards,
Taco
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,748
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

.. good that it works, again much thanks!
You're welcome. Glad you got it going. (y)

You were asking me if I was looking for a formula solution. Is there any?
Here is one way with a helper column (that could be hidden)

Taco G.xlsm
ABCDE
1MAKT.MATNRMAKT.MAKTXMARD.LABST591New list
2R600201000EDC Outlet Cast Y piece Assembly1.001EDC Outlet Cast Y piece Assembly
3R600201010Cast Return Bend + Thermow. Fitting Arr.3.002Cast Return Bend + Thermow. Fitting Arr.
4R600201020Cast Return Bend Without Thermow. Fittin3.005Cast Return Bend + Thermow. Fitting Arr.
5R600201030Impeller (GAD 3118C-19003-01 pos 14)1.008Cast Return Bend + Thermow. Fitting Arr.
6R600201040Shaft short(GAD 3118C-19003-01 pos 21)1.009Cast Return Bend Without Thermow. Fittin
7R600201060Impeller (GAD 311BB-19003-01 pos 14)1.0010Cast Return Bend Without Thermow. Fittin
8R600201070Shaft long(GAD 311BB-19003-01 pos 21)1.0011Cast Return Bend Without Thermow. Fittin
9R600201080Cast Pin R201A/B (1613FHA-9)2.0012Impeller (GAD 3118C-19003-01 pos 14)
10R600201090Cast Pin R201A/B (1613FHA-10)2.0014Shaft short(GAD 3118C-19003-01 pos 21)
11R600201100Cast Pin R201A/B (1613FHA-11)8.0016Impeller (GAD 311BB-19003-01 pos 14)
12R600201130Tub Support R201C (1249T/01-07)10.0024Shaft long(GAD 311BB-19003-01 pos 21)
13R600201110Cast Pin R201D (1623FHA-16)6.0034Cast Pin R201A/B (1613FHA-9)
14R600201120Washer R201D (1623FHA-20)6.0040Cast Pin R201A/B (1613FHA-9)
15R636815110Inducer Dickow NCLih 100/250 P0452B/C1.0046Cast Pin R201A/B (1613FHA-10)
16R636815100Waaier Dickow NCLih 100/250 P0452B/C1.0047Cast Pin R201A/B (1613FHA-10)
17R695731500Regelkl. HW9000 DN40 600Lbs RF STD 1.0611.0048Cast Pin R201A/B (1613FHA-11)
18R688650300Revisieset tbv Adams terugsl. DN350/PN101.0049Cast Pin R201A/B (1613FHA-11)
19R030510000Pijp CuNi70/30 1" x 2.11mm x 6500mm520.0050Cast Pin R201A/B (1613FHA-11)
20R910891010Pressure controller FRS-520 10-30mBarg2.00570Cast Pin R201A/B (1613FHA-11)
21R060102050Pakking Gore-Tex DN50 PN16 GN5010.00572Cast Pin R201A/B (1613FHA-11)
22R695741000Groth drukveiligheid 6" 150lbs CS/PTFE2.00572Cast Pin R201A/B (1613FHA-11)
23R695732250Sprinklerkl. GEFA/NORBRO DN100/PN1616.00574Cast Pin R201A/B (1613FHA-11)
24R733501000Regelkl. CAMFLEX II 2" 150# Wafer CV532.00590Cast Pin R201A/B (1613FHA-11)
25592Tub Support R201C (1249T/01-07)
26592Tub Support R201C (1249T/01-07)
27592Tub Support R201C (1249T/01-07)
28592Tub Support R201C (1249T/01-07)
29592Tub Support R201C (1249T/01-07)
30592Tub Support R201C (1249T/01-07)
31Tub Support R201C (1249T/01-07)
32Tub Support R201C (1249T/01-07)
33Tub Support R201C (1249T/01-07)
34Tub Support R201C (1249T/01-07)
35Cast Pin R201D (1623FHA-16)
Sheet1 (2)
Cell Formulas
RangeFormula
D1D1=MAX(D2:D30)-1
D3:D30D3=D2+C2
E2:E35E2=IF(ROWS(E$2:E2)>D$1,"",LOOKUP(ROWS(E$2:E2),D$2:D$30,B$2:B$30))
 

Taco G

New Member
Joined
Sep 7, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Hi Peter,

Thank you so much again for the formula solution. I will see if I understand the formula's and get it working like that. I am already learning a lot from you :)
Have a nice weekend!

best regards,
Taco
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,748
Office Version
  1. 365
Platform
  1. Windows
OK, good luck with it!
 

Forum statistics

Threads
1,147,744
Messages
5,742,948
Members
423,766
Latest member
dafydd_jwc

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