Sorting data
 

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

Sorting data
 

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

Sorting data
 

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

Sorting data
 

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

Sorting data
 

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

Sorting data
 

Hi Tom,
Yes this was just a simple "example" from a newbee like me in excel
programming sampling to other newbees like me what you have written in this
thread is a valid solution. I just wanted to thank you. Sorry if I appear to
be showing something else.
J_J


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

Sorting data
 

That's great. Just wasn't sure if there was another issue. Thanks.

--
Regards,
Tom Ogilvy

"J_J" wrote in message
...
Hi Tom,
Yes this was just a simple "example" from a newbee like me in excel
programming sampling to other newbees like me what you have written in
this
thread is a valid solution. I just wanted to thank you. Sorry if I appear
to
be showing something else.
J_J


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

Sorting data
 

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