Create Column Containing a value x number of times, repeat with different values

[alex0182828]

Hi All,

Sorry for the terrible explanation. What i am trying to do is very simple but i am really having trouble describing the answer is most likely in this forum or google but i can not unearth it.

I have made a screenshot showing what i am trying to achieve as its way easier to show that to explain. Any help would be great.

Basically i need the values in Column A in a list with each occurring in the list the number of times given in column B. Its so i can print of labels in specific quantities.

Any help would be great. Thanks. Alex

 

[VoG]

Try

Sub atest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    Range("D" & Rows.Count).End(xlUp).Offset(1).Resize(Range("B" & i).Value).Value = Range("A" & i).Value
Next i
End Sub

 

[Norie]

Try this.

Dim rngSrc As Range
Dim rngDst As Range
Dim LastRow As Long
Dim I As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Set rngDst = Range("D2")
    
    For I = 2 To LastRow
        Set rngSrc = Range("A" & I)
        
        rngDst.Resize(rngSrc.Offset(, 1).Value).Value = rngSrc.Value
        
        Set rngDst = rngDst.Offset(rngSrc.Offset(, 1).Value)
    Next I

 

[Weaver]

try this

Sub alex0182828()
    Range("D1") = "Output"
    r = 2
    For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        celVal = Cells(i, "A").Value
        repval = Cells(i, "B").Value
        Cells(r, "D").Resize(repval).Value = celVal
        r = r + repval
    Next i
End Sub

 

[alex0182828]

Wow what enthusiastic replies ! Checked them out and they work great.

I have used macros in the past but sadlt my intelligence does not really stretch past forumla's to understand how these work. Is there any way this could be carried out with an offset formula ?

Thanks.

 

[BenMiller]

Will the product codes all be unique?

 

[alex0182828]

Hi Ben, Yes they will be unique.

Thanks

 

[BenMiller]

I'm sorry .. I thought I might be have been able to work something out before but it kept being self-referencing; for now I'm at a mental block ...

 

[mathchick]

=IF(COUNTIF($D$2:D2,D2)"<"VLOOKUP(D2,$A$2:$B$4,2,0),D2,OFFSET($A$1,MATCH(D2,$A$2:$A$4,0)+1,,,))

except get rid of the quotes around the bracket thing. I'm having difficulties figuring out how to put that in without it making my post wonky<vlookup(d2,$a$2:$b$4,2,0),d2,offset($a$1,match(d2,$a$2:$a$4,0)+1,,,))[ code]<vlookup(d2,$a$2:$b$4,2,0),d2,offset($a$1,match(d2,$a$2:$a$4,0)+1,,,))<vlookup(d2,$a$2:$b$4,2,0),d2,offset($a$1,match(d2,$a$2:$a$4,0)+1,,,))<vlookup(d2,$a$2:$b$4,2,0),d2,offset($a$1,match(d2,$a$2:$a$4,0)+1,,,))<="" html=""></vlookup(d2,$a$2:$b$4,2,0),d2,offset($a$1,match(d2,$a$2:$a$4,0)+1,,,))[>

 

[nmathew]

Hi All,
This thread has been a life saver for me.... Thanks for posting the codes..! I have however a slightly different scenario... My requirement is exactly the same as the initial question, except for the fact that in some instances, i have 0 in col B. Is there any chance anyone can help by altering/modify the codes so that when a product code in col A have 0 labels, it wont display in col D.
Thanks in advance....