i want to arrange the data following data from one sheet to another through coding in vba for excel
100 London
100 Paris
100 Sunderland
101 usa
101 england
101 uk
in the form of
100 London Paris Sunderland
101 usa england uk
Can anybody help me..??
Faisal Ilyas
Replies (2)
Please login or register to join the discussion.
haven't you just done it
You can use what they call "copy and paste". set it up one one sheet like you have just done and then copy to a new sheet.
problemo solvados
try this
Assumes that the data starts in cell a1, and is in one column. Puts the answer in a table starting in cell c1
Sub SortIT()
Dim Capture As String
Dim test As String
Dim OutCount As Integer
OutCount = 1
Range("a1").Activate
Capture = Left(ActiveCell, 3)
test = Left(ActiveCell, 3)
Do While ActiveCell <> 0
Capture = Capture & " " & Right(ActiveCell, Len(ActiveCell) - 4)
If test <> Left(ActiveCell.Offset(1, 0), 3) Then
'this means the next item has a new number
Cells(OutCount, 3) = Capture
Capture = Left(ActiveCell.Offset(1, 0), 3)
test = Left(ActiveCell.Offset(1, 0), 3)
OutCount = OutCount + 1
End If
ActiveCell.Offset(1, 0).Activate
Loop
End Sub