Sorting data

[Guest]

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 Ogilvy]

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

 

[J_J]

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]

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.

 

[Tom Ogilvy]

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!

 

[J_J]

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]

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

 

[J_J]

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