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

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

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

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

Hi Tom,

Added a small sort routine such as:
'--------------
Sub Sort_it()
Range("A1:B10").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
End Sub
'--------------

just before your macro is called...
Works perfectly well.
Regards
J_J


"Tom Ogilvy" wrote in message
...
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

Hmmm, but I thought that is what I said???

If that is not the case, then I would
suggest sorting it as the first step of the macro.

Am I missing something or were you just showing me how to write a sort
routine. If just putting up an example of some code that would sort the
data or verifying that sorting first would work, then thanks!

--
Regards,
Tom Ogilvy

"J_J" wrote in message
...
Hi Tom,

Added a small sort routine such as:
'--------------
Sub Sort_it()
Range("A1:B10").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
End Sub
'--------------

just before your macro is called...
Works perfectly well.
Regards
J_J


"Tom Ogilvy" wrote in message
...
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