reformatting a table

[firstfoot]

I have a spreadsheet with songtitles associated to a CD catlaogue numbe
that is laid out like this:

[image: http://www.firstfoot.com/Library/images/excelsheet.jpg]

To get it into a format to impert it into a database, I need to make i
look like this:

[image: http://www.firstfoot.com/Library/images/excelsheet2.jpg]

Where the song title is horizontally aligned with each catalogu
number.

But I just can't suss the macro to do it.

My head is sore from banging it on the screen. Any ideas

 

[Bob Phillips]

Here is one way

Sub CDCollection()
Dim cLastRow As Long
Dim rng As Range
Dim i As Long

Columns("A:A").Cut
Range("C1").Insert Shift:=xlToRight
cLastRow = Cells(Rows.Count, "A").End(xlUp).row
For i = cLastRow To 2 Step -1
If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
Cells(i, "A").Offset(0, 1).Resize(1, 20).Copy _
Destination:=Cells(i - 1, "C")
If rng Is Nothing Then
Set rng = Cells(i, "A")
Else
Set rng = Union(rng, Cells(i, "A"))
End If
End If
Next i

If Not rng Is Nothing Then
rng.EntireRow.Delete
End If
Range("A1").Select

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[firstfoot]

Bob

Many thanks for that.

It worked a treat and has saved me from inflicting more pain on m
already severely bruised head: