making a list out of a list with quantities

[Taco G]

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
R600201000	EDC Outlet Cast Y piece Assembly	1,00​		EDC Outlet Cast Y piece Assembly
R600201010	Cast Return Bend + Thermow. Fitting Arr.	3,00​		Cast Return Bend + Thermow. Fitting Arr.
R600201020	Cast Return Bend Without Thermow. Fittin	3,00​		Cast Return Bend + Thermow. Fitting Arr.
R600201030	Impeller (GAD 3118C-19003-01 pos 14)	1,00​		Cast Return Bend + Thermow. Fitting Arr.
R600201040	Shaft short(GAD 3118C-19003-01 pos 21)	1,00​		Cast Return Bend Without Thermow. Fittin
R600201060	Impeller (GAD 311BB-19003-01 pos 14)	1,00​		Cast Return Bend Without Thermow. Fittin
R600201070	Shaft long(GAD 311BB-19003-01 pos 21)	1,00​		Cast Return Bend Without Thermow. Fittin
R600201080	Cast Pin R201A/B (1613FHA-9)	2,00​		Impeller (GAD 3118C-19003-01 pos 14)
R600201090	Cast Pin R201A/B (1613FHA-10)	2,00​		Shaft short(GAD 3118C-19003-01 pos 21)
R600201100	Cast Pin R201A/B (1613FHA-11)	8,00​		Impeller (GAD 311BB-19003-01 pos 14)
R600201130	Tub Support R201C (1249T/01-07)	10,00​		Shaft long(GAD 311BB-19003-01 pos 21)
R600201110	Cast Pin R201D (1623FHA-16)	6,00​		Cast Pin R201A/B (1613FHA-9)
R600201120	Washer R201D (1623FHA-20)	6,00​		Cast Pin R201A/B (1613FHA-9)
R636815110	Inducer Dickow NCLih 100/250 P0452B/C	1,00​		etc etc….
R636815100	Waaier Dickow NCLih 100/250 P0452B/C	1,00​
R695731500	Regelkl. HW9000 DN40 600Lbs RF STD 1.061	1,00​
R688650300	Revisieset tbv Adams terugsl. DN350/PN10	1,00​
R030510000	Pijp CuNi70/30 1" x 2.11mm x 6500mm	520,00​
R910891010	Pressure controller FRS-520 10-30mBarg	2,00​
R060102050	Pakking Gore-Tex DN50 PN16 GN501	0,00​
R695741000	Groth drukveiligheid 6" 150lbs CS/PTFE	2,00​
R695732250	Sprinklerkl. GEFA/NORBRO DN100/PN16	16,00​
R733501000	Regelkl. CAMFLEX II 2" 150# Wafer CV53	0,00​

 

[Peter_SSs]

Welcome to the MrExcel board!

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

 

[Peter_SSs]

Here is a macro solution.

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
	A	B	C	D	E
1	MAKT.MATNR	MAKT.MAKTX	MARD.LABST		New list
2	R600201000	EDC Outlet Cast Y piece Assembly	1.00		EDC Outlet Cast Y piece Assembly
3	R600201010	Cast Return Bend + Thermow. Fitting Arr.	3.00		Cast Return Bend + Thermow. Fitting Arr.
4	R600201020	Cast Return Bend Without Thermow. Fittin	3.00		Cast Return Bend + Thermow. Fitting Arr.
5	R600201030	Impeller (GAD 3118C-19003-01 pos 14)	1.00		Cast Return Bend + Thermow. Fitting Arr.
6	R600201040	Shaft short(GAD 3118C-19003-01 pos 21)	1.00		Cast Return Bend Without Thermow. Fittin
7	R600201060	Impeller (GAD 311BB-19003-01 pos 14)	1.00		Cast Return Bend Without Thermow. Fittin
8	R600201070	Shaft long(GAD 311BB-19003-01 pos 21)	1.00		Cast Return Bend Without Thermow. Fittin
9	R600201080	Cast Pin R201A/B (1613FHA-9)	2.00		Impeller (GAD 3118C-19003-01 pos 14)
10	R600201090	Cast Pin R201A/B (1613FHA-10)	2.00		Shaft short(GAD 3118C-19003-01 pos 21)
11	R600201100	Cast Pin R201A/B (1613FHA-11)	8.00		Impeller (GAD 311BB-19003-01 pos 14)
12	R600201130	Tub Support R201C (1249T/01-07)	10.00		Shaft long(GAD 311BB-19003-01 pos 21)
13	R600201110	Cast Pin R201D (1623FHA-16)	6.00		Cast Pin R201A/B (1613FHA-9)
14	R600201120	Washer R201D (1623FHA-20)	6.00		Cast Pin R201A/B (1613FHA-9)
15	R636815110	Inducer Dickow NCLih 100/250 P0452B/C	1.00		Cast Pin R201A/B (1613FHA-10)
16	R636815100	Waaier Dickow NCLih 100/250 P0452B/C	1.00		Cast Pin R201A/B (1613FHA-10)
17	R695731500	Regelkl. HW9000 DN40 600Lbs RF STD 1.061	1.00		Cast Pin R201A/B (1613FHA-11)
18	R688650300	Revisieset tbv Adams terugsl. DN350/PN10	1.00		Cast Pin R201A/B (1613FHA-11)
19	R030510000	Pijp CuNi70/30 1" x 2.11mm x 6500mm	520.00		Cast Pin R201A/B (1613FHA-11)
20	R910891010	Pressure controller FRS-520 10-30mBarg	2.00		Cast Pin R201A/B (1613FHA-11)
21	R060102050	Pakking Gore-Tex DN50 PN16 GN501	0.00		Cast Pin R201A/B (1613FHA-11)
22	R695741000	Groth drukveiligheid 6" 150lbs CS/PTFE	2.00		Cast Pin R201A/B (1613FHA-11)
23	R695732250	Sprinklerkl. GEFA/NORBRO DN100/PN16	16.00		Cast Pin R201A/B (1613FHA-11)
24	R733501000	Regelkl. CAMFLEX II 2" 150# Wafer CV53	0.00		Cast Pin R201A/B (1613FHA-11)
25					Tub Support R201C (1249T/01-07)
26					Tub Support R201C (1249T/01-07)
27					Tub Support R201C (1249T/01-07)
28					Tub Support R201C (1249T/01-07)
29					Tub Support R201C (1249T/01-07)
30					Tub Support R201C (1249T/01-07)
31					Tub Support R201C (1249T/01-07)
32					Tub Support R201C (1249T/01-07)
33					Tub Support R201C (1249T/01-07)
34					Tub Support R201C (1249T/01-07)
35					Cast Pin R201D (1623FHA-16)
Sheet1

 

[Taco G]

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]

.. good that it works, again much thanks!

You're welcome. Glad you got it going.

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
	A	B	C	D	E
1	MAKT.MATNR	MAKT.MAKTX	MARD.LABST	591	New list
2	R600201000	EDC Outlet Cast Y piece Assembly	1.00	1	EDC Outlet Cast Y piece Assembly
3	R600201010	Cast Return Bend + Thermow. Fitting Arr.	3.00	2	Cast Return Bend + Thermow. Fitting Arr.
4	R600201020	Cast Return Bend Without Thermow. Fittin	3.00	5	Cast Return Bend + Thermow. Fitting Arr.
5	R600201030	Impeller (GAD 3118C-19003-01 pos 14)	1.00	8	Cast Return Bend + Thermow. Fitting Arr.
6	R600201040	Shaft short(GAD 3118C-19003-01 pos 21)	1.00	9	Cast Return Bend Without Thermow. Fittin
7	R600201060	Impeller (GAD 311BB-19003-01 pos 14)	1.00	10	Cast Return Bend Without Thermow. Fittin
8	R600201070	Shaft long(GAD 311BB-19003-01 pos 21)	1.00	11	Cast Return Bend Without Thermow. Fittin
9	R600201080	Cast Pin R201A/B (1613FHA-9)	2.00	12	Impeller (GAD 3118C-19003-01 pos 14)
10	R600201090	Cast Pin R201A/B (1613FHA-10)	2.00	14	Shaft short(GAD 3118C-19003-01 pos 21)
11	R600201100	Cast Pin R201A/B (1613FHA-11)	8.00	16	Impeller (GAD 311BB-19003-01 pos 14)
12	R600201130	Tub Support R201C (1249T/01-07)	10.00	24	Shaft long(GAD 311BB-19003-01 pos 21)
13	R600201110	Cast Pin R201D (1623FHA-16)	6.00	34	Cast Pin R201A/B (1613FHA-9)
14	R600201120	Washer R201D (1623FHA-20)	6.00	40	Cast Pin R201A/B (1613FHA-9)
15	R636815110	Inducer Dickow NCLih 100/250 P0452B/C	1.00	46	Cast Pin R201A/B (1613FHA-10)
16	R636815100	Waaier Dickow NCLih 100/250 P0452B/C	1.00	47	Cast Pin R201A/B (1613FHA-10)
17	R695731500	Regelkl. HW9000 DN40 600Lbs RF STD 1.061	1.00	48	Cast Pin R201A/B (1613FHA-11)
18	R688650300	Revisieset tbv Adams terugsl. DN350/PN10	1.00	49	Cast Pin R201A/B (1613FHA-11)
19	R030510000	Pijp CuNi70/30 1" x 2.11mm x 6500mm	520.00	50	Cast Pin R201A/B (1613FHA-11)
20	R910891010	Pressure controller FRS-520 10-30mBarg	2.00	570	Cast Pin R201A/B (1613FHA-11)
21	R060102050	Pakking Gore-Tex DN50 PN16 GN501	0.00	572	Cast Pin R201A/B (1613FHA-11)
22	R695741000	Groth drukveiligheid 6" 150lbs CS/PTFE	2.00	572	Cast Pin R201A/B (1613FHA-11)
23	R695732250	Sprinklerkl. GEFA/NORBRO DN100/PN16	16.00	574	Cast Pin R201A/B (1613FHA-11)
24	R733501000	Regelkl. CAMFLEX II 2" 150# Wafer CV53	2.00	590	Cast Pin R201A/B (1613FHA-11)
25				592	Tub Support R201C (1249T/01-07)
26				592	Tub Support R201C (1249T/01-07)
27				592	Tub Support R201C (1249T/01-07)
28				592	Tub Support R201C (1249T/01-07)
29				592	Tub Support R201C (1249T/01-07)
30				592	Tub Support R201C (1249T/01-07)
31					Tub Support R201C (1249T/01-07)
32					Tub Support R201C (1249T/01-07)
33					Tub Support R201C (1249T/01-07)
34					Tub Support R201C (1249T/01-07)
35					Cast Pin R201D (1623FHA-16)
Sheet1 (2)

Cell Formulas
Range	Formula
D1	D1	=MAX(D2:D30)-1
D3:D30	D3	=D2+C2
E2:E35	E2	=IF(ROWS(E$2:E2)>D$1,"",LOOKUP(ROWS(E$2:E2),D$2:D$30,B$2:B$30))

 

[Taco G]

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]

OK, good luck with it!