sorting problem
 

Hi!

I have problems generating a rutine that will automatically sort my
sheet.
The sheet is in this format (always 2 columns only the rows may vary)

a 1
a 2
a 3
b 2
b 1
c 7
d 1
d 3
d 4
d 6
d 7
e 7


I want it to look like this:

a b c d e
1 2 7 1 7
2 1 3
3 4
6
7

Anyone know what vba code to use to get this done?

/Colargol

hi
this is not a sorting problem. it's a transposing problem.
I pasted your two columns in a blank workbook and wrote
this macro. the result was what you want it to look like.
in my set up, the two columns of data were in columns a
and b starting at a1 with a header. the data was
transposed to columns d,c,e,f,g starting at d1. if that is
not where you want it, you will have to go through the
code and make some changes. i make were you may wish to
make changes. it worked on my machine.

Sub macTranspose()
Dim h1 As Range
Dim h2 As Range
'macro assume data is at A1 down and has a header
Range(Range("A2"), Range("A2").End(xlDown)).Copy
'change this range if you want the data to be reduced
'to one each somewhere else
Range("AA1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Set h1 = Range("aa1")
Do While Not IsEmpty(h1)
Set h2 = h1.Offset(1, 0)
If h1 = h2 Then
h2.Delete Shift:=xlUp
Else
Set h1 = h2
End If
Loop
Range(Range("AA1"), Range("AA1").End(xlDown)).Copy
'change range("D1") if you want it transposed somewhere
else
Range("D1").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Dim h3 As Range
Dim d1 As Range
Dim d2
Dim v1
Set h1 = Range("D1")
Set d1 = Range("A2")
Do While Not IsEmpty(d1)
Set h2 = h1.Offset(0, 1)
Set h3 = h1.Offset(65000, 0).End(xlUp).Offset(1, 0)
Set d2 = d1.Offset(1, 0)
Set v1 = d1.Offset(0, 1)
If h1.Value = d1.Value Then
h3.Value = v1.Value
If d1 < d2 Then
Set h1 = h2
End If
Set d1 = d2
End If
Loop
End Sub
-----Original Message-----
Hi!

I have problems generating a rutine that will
automatically sort my
sheet.
The sheet is in this format (always 2 columns only the
rows may vary)

a 1
a 2
a 3
b 2
b 1
c 7
d 1
d 3
d 4
d 6
d 7
e 7


I want it to look like this:

a b c d e
1 2 7 1 7
2 1 3
3 4
6
7

Anyone know what vba code to use to get this done?

/Colargol
.

hi again.
i leave work in about 40 minutes. if you have further
questions, post then and i will answer tomorrow.

-----Original Message-----
hi
this is not a sorting problem. it's a transposing problem.
I pasted your two columns in a blank workbook and wrote
this macro. the result was what you want it to look like.
in my set up, the two columns of data were in columns a
and b starting at a1 with a header. the data was
transposed to columns d,c,e,f,g starting at d1. if that
is
not where you want it, you will have to go through the
code and make some changes. i make were you may wish to
make changes. it worked on my machine.

Sub macTranspose()
Dim h1 As Range
Dim h2 As Range
'macro assume data is at A1 down and has a header
Range(Range("A2"), Range("A2").End(xlDown)).Copy
'change this range if you want the data to be reduced
'to one each somewhere else
Range("AA1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Set h1 = Range("aa1")
Do While Not IsEmpty(h1)
Set h2 = h1.Offset(1, 0)
If h1 = h2 Then
h2.Delete Shift:=xlUp
Else
Set h1 = h2
End If
Loop
Range(Range("AA1"), Range("AA1").End(xlDown)).Copy
'change range("D1") if you want it transposed somewhere
else
Range("D1").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Dim h3 As Range
Dim d1 As Range
Dim d2
Dim v1
Set h1 = Range("D1")
Set d1 = Range("A2")
Do While Not IsEmpty(d1)
Set h2 = h1.Offset(0, 1)
Set h3 = h1.Offset(65000, 0).End(xlUp).Offset(1, 0)
Set d2 = d1.Offset(1, 0)
Set v1 = d1.Offset(0, 1)
If h1.Value = d1.Value Then
h3.Value = v1.Value
If d1 < d2 Then
Set h1 = h2
End If
Set d1 = d2
End If
Loop
End Sub
-----Original Message-----
Hi!

I have problems generating a rutine that will
automatically sort my
sheet.
The sheet is in this format (always 2 columns only the
rows may vary)

a 1
a 2
a 3
b 2
b 1
c 7
d 1
d 3
d 4
d 6
d 7
e 7


I want it to look like this:

a b c d e
1 2 7 1 7
2 1 3
3 4
6
7

Anyone know what vba code to use to get this done?

/Colargol
.

.

What can I say... works like a charm.
Thank you so very much! :-)))))


On Thu, 17 Feb 2005 12:20:09 -0800,
wrote:

hi again.
i leave work in about 40 minutes. if you have further
questions, post then and i will answer tomorrow.

-----Original Message-----
hi
this is not a sorting problem. it's a transposing problem.
I pasted your two columns in a blank workbook and wrote
this macro. the result was what you want it to look like.
in my set up, the two columns of data were in columns a
and b starting at a1 with a header. the data was
transposed to columns d,c,e,f,g starting at d1. if that
is
not where you want it, you will have to go through the
code and make some changes. i make were you may wish to
make changes. it worked on my machine.

Sub macTranspose()
Dim h1 As Range
Dim h2 As Range
'macro assume data is at A1 down and has a header
Range(Range("A2"), Range("A2").End(xlDown)).Copy
'change this range if you want the data to be reduced
'to one each somewhere else
Range("AA1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Set h1 = Range("aa1")
Do While Not IsEmpty(h1)
Set h2 = h1.Offset(1, 0)
If h1 = h2 Then
h2.Delete Shift:=xlUp
Else
Set h1 = h2
End If
Loop
Range(Range("AA1"), Range("AA1").End(xlDown)).Copy
'change range("D1") if you want it transposed somewhere
else
Range("D1").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Dim h3 As Range
Dim d1 As Range
Dim d2
Dim v1
Set h1 = Range("D1")
Set d1 = Range("A2")
Do While Not IsEmpty(d1)
Set h2 = h1.Offset(0, 1)
Set h3 = h1.Offset(65000, 0).End(xlUp).Offset(1, 0)
Set d2 = d1.Offset(1, 0)
Set v1 = d1.Offset(0, 1)
If h1.Value = d1.Value Then
h3.Value = v1.Value
If d1 < d2 Then
Set h1 = h2
End If
Set d1 = d2
End If
Loop
End Sub
-----Original Message-----
Hi!

I have problems generating a rutine that will
automatically sort my
sheet.
The sheet is in this format (always 2 columns only the
rows may vary)

a 1
a 2
a 3
b 2
b 1
c 7
d 1
d 3
d 4
d 6
d 7
e 7


I want it to look like this:

a b c d e
1 2 7 1 7
2 1 3
3 4
6
7

Anyone know what vba code to use to get this done?

/Colargol
.

.