VBA to create new list with number veing number of times each product shows?

[tonywatsonhelp]

Hi Everyone,

Ok,

So Column K has Items listed in it
and Column L Has the number of items ordered

So
K L
ToyBOX 20 for example(carries on down the column with different Items on)

so In Column P I want a macro that will go down column K look at the number column L and create alit on that many of the items.

Example

	K	L		P
1	Item	Quantity		New Item list I want
2	Bag1	2		Bag1<strike></strike>
3	Hat	4		Bag1<strike></strike>
4	scaff	1		Hat<strike></strike>
5	vood	5		Hat<strike></strike>
6				Hat<strike></strike>
7				Hat<strike></strike>
8				scaff<strike></strike>
9				vood<strike></strike>
10				vood<strike></strike>
11				vood<strike></strike>
12				vood<strike></strike>
13				vood<strike></strike>
14
15

<tbody>
</tbody>

 

[dmt32]

Hi,
not tested but see if this does what you want

Sub MakeList()
    Dim OrderList As Variant
    Dim QtyList() As String
    Dim i As Integer, qty As Integer, q As Integer
    
    OrderList = Range(Range("K2"), Range("L" & Rows.Count).End(xlUp)).Value
    
    q = 1
    qty = 1
    For i = 1 To UBound(OrderList, 1)
        qty = qty + OrderList(i, 2)
        ReDim Preserve QtyList(1 To qty)
        For q = q To qty
            QtyList(q) = OrderList(i, 1)
        Next
        q = qty
    Next i
        
    Range("P2").Resize(qty - 1, 1).Value = Application.Transpose(QtyList)
        
End Sub

adjust code as required.

Dave

 

[tonywatsonhelp]

Brilliant,
Thanks Dave