Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 . . |
#4
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting problem, sums change | Excel Discussion (Misc queries) | |||
Sorting problem | Excel Discussion (Misc queries) | |||
Difficult Sorting Problem | Excel Discussion (Misc queries) | |||
Sorting problem | Excel Discussion (Misc queries) | |||
Sorting problem | Excel Worksheet Functions |