The example was given as sorted, so I made the assumption that the
requirement was for sorted data. If that is not the case, then I would
suggest sorting it as the first step of the macro. If that is not possible
(unlikely since the data is replaced by the macro), then I would suggest
copying it to another sheet and sorting it, as the first step of the macro.

--
Regards,
Tom Ogilvy

"J_J" wrote in message
...
Tom,

Joining the thread with your permission, I think that your solution
"assumes" (or the given data appears to be) that in the given data all
similar items are ALWAYS grouped on the list. Because I've tried you code
with some arbitrary data e.g.

X 1
X 1
X 1
Y 1
X 1
Y 1
Z 1

and it failed to display it like this !.
X 1 Y 1 Z 1
X 1 Y 1
X 1
X 1

Of course I am not sure if the task was to accomplish this. If not,
hope you can modify it to be able to do that too...
Best wishes and TIA
J_J




"Tom Ogilvy" wrote in message
...
Test this on a copy of your data.

Sub ReorderData()
Dim rng As Range, cell As Range
Dim icol As Long, irow As Long
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
icol = 3
irow = 1
For Each cell In rng
If cell.Row = 1 Then
Cells(1, 3).Value = Cells(1, 1).Value
Cells(1, 4).Value = Cells(1, 2).Value
irow = irow + 1
Else
If cell.Value < cell(0).Value Then
icol = icol + 2
irow = 1
End If
Cells(irow, icol).Value = cell.Value
Cells(irow, icol + 1).Value = cell.Offset(0, 1).Value
irow = irow + 1
End If
Next
Columns(1).Resize(, 2).EntireColumn.Delete
End Sub

--
Regards,
Tom Ogilvy


"Rain" wrote in message
...
Hello!

I have data on a sheet in the form
Col1 Col2
X 1
X 1
X 1
Y 1
Y 1

Now, I want to change this to the format below using vba

col1 col2 col3 col4
X 1 Y 1
X 1 Y 1
X 1

Any easy way to do it?

regards,
rain