"re-pivot"

[lineto]

Hi everyone,

I have a list formatted as follows (two columns):

A 2
B 1
C 4

and I want to convert this to this list (one column):

A
A
B
C
C
C
C

... any ideas?
Thanks in advance, lineto.

 

[jeffreybrown]

How about this...

Sub OneCol()
    Dim LR As Long: LR = Range("A" & Rows.Count).End(xlUp).Row
    Dim LR1 As Long
    Dim i As Long

    For i = 1 To LR
        If Range("B" & i).Value > 1 Then
            LR1 = Range("A" & Rows.Count).End(xlUp).Offset(1).Row
            Range("A" & i).Copy _
                Range("A" & LR1).Resize(Range("B" & i).Value - 1)
        End If
    Next i
    Columns(2).Delete
    Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Sort _
    Key1:=Range("$A$1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub

 

[lineto]

Hi Jeffrey,
thanks a lot! I have forgot to say that I am using Excel 2008 on a Mac which isn't supporting Macros anymore -- are there any other possibilities to solve this problem?

Thanks, again!

 

[lineto]

Hi Jeffrey,
changed the version and it works perfect -- thanks!

 

[jeffreybrown]

Hi lineto,

You're welcome...Glad it worked out for you