How To Repeat SKU Based On Qty?

[xlmaniac]

Dear All,
I would like to repeat the SKU number(vertically) based on the qty of the SKU.
I am trying to create a template where there will be an input field and an output field(Desired Result).
In the input field, there will be SKU & their corresponding qty. The qty range would vary between 1 to 100 maximum. At a time a maximum of 600 SKU will be entered in the input field along with their corresponding qty.
The formula in the desired column should repeat the SKU number vertically, that many times as that of the qty. Also the range will be continuous and there would be no gap between 2 SKUs.
For example, I have a small data set where I have got 4 different SKUs and their corresponding Qty across A1:B6.
The formula in the Desired columnD, should repeat the SKUs multiple times based on the qty mentioned against the SKUs.
The SKU no:-122001 should be repeated twice under the D column followed by article 134569(thrice),followed by article 178906(10 times) & 187652(5 times).
Could somebody help me out with the formula in Column D which can yield the desired result?
Thanks in advance.
Sheet1

*	A	B	C	D
1	Input Field	*	Desired Result
2	SKU	Qty	*	SKU
3	122001	2	*	122001
4	134569	3	*	122001
5	178906	10	*	134569
6	187652	5	*	134569
7	*	*	*	134569
8	*	*	*	178906
9	*	*	*	178906
10	*	*	*	178906
11	*	*	*	178906
12	*	*	*	178906
13	*	*	*	178906
14	*	*	*	178906
15	*	*	*	178906
16	*	*	*	178906
17	*	*	*	178906
18	*	*	*	187652
19	*	*	*	187652
20	*	*	*	187652
21	*	*	*	187652
22	*	*	*	187652

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:93px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[hiker95]

xlmaniac,

I think that my screenshots were not correct, based on your original screenshot.

Here is another macro solution for you to consider.

Sample raw data, and, results:

Excel 2007
	A	B	C	D
1	Input Field
2	SKU	Qty		SKU
3	122001	2		122001
4	134569	3		122001
5	178906	10		134569
6	187652	5		134569
7				134569
8				178906
9				178906
10				178906
11				178906
12				178906
13				178906
14				178906
15				178906
16				178906
17				178906
18				187652
19				187652
20				187652
21				187652
22				187652
23
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub RepeatSKUsV2()
' hiker95, 06/15/2015, ME861602
Dim a As Variant, o As Variant
Dim i As Long, j As Long, n As Long
With Sheets("Sheet1")
  a = .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row)
  n = Evaluate("=Sum(B3" & ":B" & UBound(a, 1) + 1 & ")")
  ReDim o(1 To n, 1 To 1)
  For i = 2 To UBound(a, 1)
    For n = 1 To a(i, 2)
      j = j + 1: o(j, 1) = a(i, 1)
    Next n
  Next i
  .Columns(4).ClearContents
  .Cells(2, 4) = "SKU"
  .Cells(3, 4).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(4).AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the RepeatSKUsV2 macro.

 

[xlmaniac]

xlmaniac,

I think that my screenshots were not correct, based on your original screenshot.

Here is another macro solution for you to consider.

Sample raw data, and, results:

Excel 2007

	A	B	C	D
1	Input Field
2	SKU	Qty		SKU
3	122001	2		122001
4	134569	3		122001
5	178906	10		134569
6	187652	5		134569
7				134569
8				178906
9				178906
10				178906
11				178906
12				178906
13				178906
14				178906
15				178906
16				178906
17				178906
18				187652
19				187652
20				187652
21				187652
22				187652
23

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub RepeatSKUsV2()
' hiker95, 06/15/2015, ME861602
Dim a As Variant, o As Variant
Dim i As Long, j As Long, n As Long
With Sheets("Sheet1")
  a = .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row)
  n = Evaluate("=Sum(B3" & ":B" & UBound(a, 1) + 1 & ")")
  ReDim o(1 To n, 1 To 1)
  For i = 2 To UBound(a, 1)
    For n = 1 To a(i, 2)
      j = j + 1: o(j, 1) = a(i, 1)
    Next n
  Next i
  .Columns(4).ClearContents
  .Cells(2, 4) = "SKU"
  .Cells(3, 4).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(4).AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the RepeatSKUsV2 macro.

Hi Hiker95,
Thank you so much for the solution!!
This time it is working fine for me without any error.
Pls accept my sincere gratitude for taking your time out and helping me with an excellent solution!!
Really appreciate your support.
Feel honoured to learn excel from excel gurus like you!!
Regards

 

[hiker95]

xlmaniac,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.