Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
1D Array Sort
I have a single dimension array that I would like sorted, and I am just
drawing a blank. Thanks, folks. -- Trent Argante [DC.J(549)] |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
1D Array Sort
What have you tried?
This looks like a school assignment and nobody will help you along with that. Some may point you in the right direction if you prove that you're actually trying and heading in the wrong direction, but none will do it for you. Cheers, Jason Lepack On Apr 7, 2:31 pm, Trent Argante wrote: I have a single dimension array that I would like sorted, and I am just drawing a blank. Thanks, folks. -- Trent Argante [DC.J(549)] |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
1D Array Sort
Google may name, you'll see I'm not in school. I'm at work, and have been
thinking about it all weekend. Like I said, I'm drawing a blank. Please, if you're not going to help, don't reply. Keep it professional. -- Trent Argante [DC.J(549)] "Jason Lepack" wrote: What have you tried? This looks like a school assignment and nobody will help you along with that. Some may point you in the right direction if you prove that you're actually trying and heading in the wrong direction, but none will do it for you. Cheers, Jason Lepack On Apr 7, 2:31 pm, Trent Argante wrote: I have a single dimension array that I would like sorted, and I am just drawing a blank. Thanks, folks. -- Trent Argante [DC.J(549)] |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
1D Array Sort
Fair enough.
More information is still required. People here are more than willing to help but you need to provide a starting point. Why do you need an array, how is the array declared, is this in VBA or are you looking a spreadsheet formula? Please be professional and give an accurate description of the problem with as much detail as possible so that a solution can be given. Cheers, Jason Lepack On Apr 7, 2:52 pm, Trent Argante wrote: Google may name, you'll see I'm not in school. I'm at work, and have been thinking about it all weekend. Like I said, I'm drawing a blank. Please, if you're not going to help, don't reply. Keep it professional. -- Trent Argante [DC.J(549)] "Jason Lepack" wrote: What have you tried? This looks like a school assignment and nobody will help you along with that. Some may point you in the right direction if you prove that you're actually trying and heading in the wrong direction, but none will do it for you. Cheers, Jason Lepack On Apr 7, 2:31 pm, Trent Argante wrote: I have a single dimension array that I would like sorted, and I am just drawing a blank. Thanks, folks. -- Trent Argante [DC.J(549)] |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
1D Array Sort
Trent Argante wrote:
Google may name, you'll see I'm not in school. I'm at work, and have been thinking about it all weekend. Like I said, I'm drawing a blank. Please, if you're not going to help, don't reply. Keep it professional. Googling Sorting Arrays in Excel would give you some help. One that works for me for many occasions is Sub QuickSort(VA_array, Optional V_Low1, Optional V_high1) On Error Resume Next 'Dimension variables Dim V_Low2, V_high2, V_loop As Integer Dim V_val1, V_val2 As Variant 'If first time, get the size of the array to sort If IsMissing(V_Low1) Then V_Low1 = LBound(VA_array, 1) End If If IsMissing(V_high1) Then V_high1 = UBound(VA_array, 1) End If 'Set new extremes to old extremes V_Low2 = V_Low1 V_high2 = V_high1 'Get value of array item in middle of new extremes V_val1 = VA_array((V_Low1 + V_high1) / 2) 'Loop for all the items in the array between the extremes While (V_Low2 <= V_high2) 'Find the first item that is greater than the mid-point item While (VA_array(V_Low2) < V_val1 And V_Low2 < V_high1) V_Low2 = V_Low2 + 1 Wend 'Find the last item that is less than the mid-point item While (VA_array(V_high2) V_val1 And V_high2 V_Low1) V_high2 = V_high2 - 1 Wend 'If the new 'greater' item comes before the new 'less' item, swap them If (V_Low2 <= V_high2) Then V_val2 = VA_array(V_Low2) VA_array(V_Low2) = VA_array(V_high2) VA_array(V_high2) = V_val2 'Advance the pointers to the next item V_Low2 = V_Low2 + 1 V_high2 = V_high2 - 1 End If Wend 'Iterate to sort the lower half of the extremes If (V_high2 V_Low1) Then Call QuickSort(VA_array, V_Low1, V_high2) 'Iterate to sort the upper half of the extremes If (V_Low2 < V_high1) Then Call QuickSort(VA_array, V_Low2, V_high1) End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
1D Array Sort
Does this work for you?
Sub SortEachColumn() Dim LastCol As Long Dim LastRow As Long Dim n As Long LastCol = Cells(1, Columns.count).End(xlToLeft).Column For n = 1 To LastCol LastRow = Cells(Rows.count, n).End(xlUp).Row Range(Cells(1, n), Cells(LastRow, n)).Sort _ key1:=Cells(1, n), order1:=xlAscending Next n End Sub If not, perhaps this will do it: =INDEX(A:A,LOOKUP(2,1/($A$1:$A$65535<""),ROW($A$1:$A$65535))-ROWS(B$1:B1)+1) Regards, Ryan-- -- RyGuy "Jason Lepack" wrote: Fair enough. More information is still required. People here are more than willing to help but you need to provide a starting point. Why do you need an array, how is the array declared, is this in VBA or are you looking a spreadsheet formula? Please be professional and give an accurate description of the problem with as much detail as possible so that a solution can be given. Cheers, Jason Lepack On Apr 7, 2:52 pm, Trent Argante wrote: Google may name, you'll see I'm not in school. I'm at work, and have been thinking about it all weekend. Like I said, I'm drawing a blank. Please, if you're not going to help, don't reply. Keep it professional. -- Trent Argante [DC.J(549)] "Jason Lepack" wrote: What have you tried? This looks like a school assignment and nobody will help you along with that. Some may point you in the right direction if you prove that you're actually trying and heading in the wrong direction, but none will do it for you. Cheers, Jason Lepack On Apr 7, 2:31 pm, Trent Argante wrote: I have a single dimension array that I would like sorted, and I am just drawing a blank. Thanks, folks. -- Trent Argante [DC.J(549)] |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
1D Array Sort
We're in the Excel.Programming group, so it's VBA. I've got a one
dimensional array, with 10 elements, random values, which came from various cells, so I can't simply sort the range. So, I'm at, how do I sort an array. It's been about 15 years since I've done any sorting programming, and I just can't get my brain to engage. I've checked out Sub QuickSort that Tom Ogilvy posted, but it's multi-dimensional, and there's a column assignment that I can't reconcile. So, I'm just stuck - brain-lock. -- Trent Argante [DC.J(549)] "Jason Lepack" wrote: Fair enough. More information is still required. People here are more than willing to help but you need to provide a starting point. Why do you need an array, how is the array declared, is this in VBA or are you looking a spreadsheet formula? Please be professional and give an accurate description of the problem with as much detail as possible so that a solution can be given. Cheers, Jason Lepack On Apr 7, 2:52 pm, Trent Argante wrote: Google may name, you'll see I'm not in school. I'm at work, and have been thinking about it all weekend. Like I said, I'm drawing a blank. Please, if you're not going to help, don't reply. Keep it professional. -- Trent Argante [DC.J(549)] "Jason Lepack" wrote: What have you tried? This looks like a school assignment and nobody will help you along with that. Some may point you in the right direction if you prove that you're actually trying and heading in the wrong direction, but none will do it for you. Cheers, Jason Lepack On Apr 7, 2:31 pm, Trent Argante wrote: I have a single dimension array that I would like sorted, and I am just drawing a blank. Thanks, folks. -- Trent Argante [DC.J(549)] |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
1D Array Sort
Let the worksheet sort help you:
1. copy the array into an unused column 2. sort the column 3. pull the values back int the array Here is a very simple example: Sub sortedaffair() s = Array(3, 1, 6, 21, 8, 5, 19, 33) For i = 0 To 7 J = i + 1 Cells(J, "B").Value = s(i) Next Columns("B:B").Sort Key1:=Range("B1") For i = 0 To 7 J = i + 1 s(i) = Cells(J, "B").Value Next End Sub -- Gary''s Student - gsnu200777 "Trent Argante" wrote: I have a single dimension array that I would like sorted, and I am just drawing a blank. Thanks, folks. -- Trent Argante [DC.J(549)] |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
1D Array Sort
I googled Sorting Arrays in Excel and got the following:
'Sort array For lLoop = 0 To UBound(MyArray) For lLoop2 = lLoop To UBound(MyArray) If MyArray(lLoop2) < MyArray(lLoop) Then ' Swap values sgl1 = MyArray(lLoop) sgl2 = MyArray(lLoop2) MyArray(lLoop) = sgl2 MyArray(lLoop2) = sgl1 End If Next lLoop2 Next lLoop Testing each element against the other is what I couldn't seem to grasp. Thank you all for your input! -- Trent Argante [DC.J(549)] "Trent Argante" wrote: I have a single dimension array that I would like sorted, and I am just drawing a blank. Thanks, folks. -- Trent Argante [DC.J(549)] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort 2D array | Excel Programming | |||
Array sort | Excel Programming | |||
Array... Sort of... | Excel Worksheet Functions | |||
Sort an Array | Excel Programming | |||
sort an array | Excel Programming |