Help?

[JC]

I have a load of data in column A

Like this

1
1
BASSHUNTER FT DJ MENTAL THEO
NOW YOU'RE GONE
2
2
ADELE
CHASING PAVEMENTS
3
6
NICKELBACK
ROCKSTAR


I need the info to be in rows

Like this : equals different rows

1 :1:BASSHUNTER FT DJ MENTAL THEO:NOW YOU'RE GONE
2 :2:ADELE:CHASING PAVEMENTS
3 :6:NICKELBACK:ROCKSTAR


Ive tried copy and paste special using the transpose etc

however i just get 1 row like this

11BASSHUNTER FT DJ MENTAL THEONOW YOU'RE GONE22ADELECHASING PAVEMENTS36NICKELBACKROCKSTAR


Any ideas?

 

[Don Guillett]

Should do it

Option Explicit
Sub makelist()
Dim i As Long
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step 4
Cells(i, "b").Formula = _
"=a" & i & "&"":""&a" & i + 1 & _
"&a" & i + 2 & "&a" & i + 3 & ""
Next i
With Columns("B")
.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
.AutoFit
End With
End Sub

 

[JC]

Thankyou much appreciated i will try it...