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
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD][/TD]
[TD]P
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Item
[/TD]
[TD]Quantity
[/TD]
[TD][/TD]
[TD]New Item list I want
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Bag1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]Bag1<strike></strike>
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Hat
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]Bag1<strike></strike>
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]scaff
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]Hat<strike></strike>
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]vood [/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]Hat<strike></strike>
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Hat<strike></strike>
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Hat<strike></strike>
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]scaff<strike></strike>
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]vood<strike></strike>
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]vood<strike></strike>
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]vood<strike></strike>
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]vood<strike></strike>
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]vood<strike></strike>
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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