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

 

[mumps]

I don't know about a formula, but this macro may do what you want:

Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim quantity As Range
    Dim x As Long
    For Each quantity In Range("B3:B" & LastRow)
        For x = 1 To quantity.Value
            Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = quantity.Offset(0, -1)
        Next x
    Next quantity
    Application.ScreenUpdating = True
End Sub

 

[sheetspread]

http://www.mrexcel.com/forum/excel-questions/834378-word-multiplied-number-create-list-2.html

See post #19 for a formula

 

[hiker95]

xlmaniac,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

Here is another macro solution for you to consider that uses two arrays in memory (very fast), and, will adjust to the varying number of raw data rows.

Sample raw data:

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

After the macro:

Excel 2007
	A	B	C	D
1	SKU	Qty		SKU
2	122001	2		122001
3	134569	3		122001
4	178906	10		134569
5	187652	5		134569
6				134569
7				178906
8				178906
9				178906
10				178906
11				178906
12				178906
13				178906
14				178906
15				178906
16				178906
17				187652
18				187652
19				187652
20				187652
21				187652
22
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 RepeatSKUs()
' 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("A1:B" & .Range("A" & Rows.Count).End(xlUp).Row)
  n = Evaluate("=Sum(B2" & ":B" & UBound(a, 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(1, 4) = "SKU"
  .Cells(2, 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 RepeatSKUs macro.

 

[xlmaniac]

I don't know about a formula, but this macro may do what you want:

Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim quantity As Range
    Dim x As Long
    For Each quantity In Range("B3:B" & LastRow)
        For x = 1 To quantity.Value
            Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = quantity.Offset(0, -1)
        Next x
    Next quantity
    Application.ScreenUpdating = True
End Sub

Hi Mumps,
Thanks for the solution.
But I am unable to proceed further(post pasting the code in the module)?
Could you just help?

 

[xlmaniac]

Hi Hiker95,
Thanks for your solution. I am using Excel 2010 and Windows7.
I will follow the steps that you have mentioned and would get back to you in case I need any further assistance from you since I am completely novice in macro.
Thanks again.

 

[xlmaniac]

Hi Hiker95,
I am getting a runtime error 13(Type Mismatch) following your steps.
Could you pls hel me in fixing this pls?
Thanks

 

[hiker95]

Hi Hiker95,
I am getting a runtime error 13(Type Mismatch) following your steps.
Could you pls hel me in fixing this pls?
Thanks

xlmaniac,

Are my screenshots correct?

 

[sheetspread]

hiker95's macro works, start with exactly the same layout, an extra space might jam

 

[xlmaniac]

xlmaniac,

Are my screenshots correct?

Hi Hiker95,
Thanks once again for all your help.
Yes your screen shots are absolutely correct.
I have also followed the exact steps that you have referred above.
But again I am getting the same error.
I think it could be some error from my end only.
I will again try sometime later(post refreshing my mind).
Thanks again