Excel: re-arrange data crossways

Excel: re-arrange data crossways

Didn't find your answer?

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.

avatar
By AnonymousUser
10th Sep 2007 10:16

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

Thanks (0)
avatar
By listerramjet
10th Sep 2007 12:41

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

Thanks (0)