# How To Repeat SKU Based On Qty?

#### xlmaniac

##### Well-known Member
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?
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

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
I don't know about a formula, but this macro may do what you want:
Code:
``````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``````

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
ABCD
1SKUQty
21220012
31345693
417890610
51876525
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

After the macro:

Excel 2007
ABCD
1SKUQtySKU
21220012122001
31345693122001
417890610134569
51876525134569
6134569
7178906
8178906
9178906
10178906
11178906
12178906
13178906
14178906
15178906
16178906
17187652
18187652
19187652
20187652
21187652
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
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.

Code:
``````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.

Last edited:
I don't know about a formula, but this macro may do what you want:
Code:
``````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?

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.

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

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?

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

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

Replies
6
Views
469
Replies
9
Views
202
Replies
3
Views
558
Replies
37
Views
3K
Replies
1
Views
526

1,196,213
Messages
6,014,032
Members
441,801
Latest member
Aneurysm

### 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.

### Which adblocker are you using?

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

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