Help me please with pivot script (Dave Peterson)

[Danny Boy via OfficeKB.com]

Hi Dave

Thanks for your response,

I'm sure that there is nothing wrong with your script but it won't work on my
workbook. Please ignore my first 2 posts. I have now changed my mined and
want to do it from the format below

I have 2 Columns will this style info:

Batman CAPER
Batman CLOWN
Batman FAIRY
Robin SIDEKICK
Robin PONSE
Robin PETAPAN



I would like a macro to output:

Batman CAPER CLOWN FAIRY
Robin SIDEKICK PONSE FAIRY

Please note that all the data will be different but it will only be two
columns.

P.S.

I also know that this can be done by pivot table. But unfortunatly, this is
not the way I want to do it.

 

[Bob Phillips]

Sub Reformat()
Dim iLastrow As Long
Dim i As Long
Dim rng As Range

iLastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastrow To 2 Step -1
If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
Cells(i - 1, "C").Resize(, 200).Value = Cells(i, "B").Resize(,
200).Value
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
End If
Next i

If Not rng Is Nothing Then rng.Delete

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

And you have another reply at your other post.

Hi Dave

Thanks for your response,

I'm sure that there is nothing wrong with your script but it won't work on my
workbook. Please ignore my first 2 posts. I have now changed my mined and
want to do it from the format below

I have 2 Columns will this style info:

Batman CAPER
Batman CLOWN
Batman FAIRY
Robin SIDEKICK
Robin PONSE
Robin PETAPAN

I would like a macro to output:

Batman CAPER CLOWN FAIRY
Robin SIDEKICK PONSE FAIRY

Please note that all the data will be different but it will only be two
columns.

P.S.

I also know that this can be done by pivot table. But unfortunatly, this is
not the way I want to do it.

 

[Danny Boy via OfficeKB.com]

Thanks alot Dave and Bob

I have managed to do what I wanted