How do you sort an array 4 elements at a time (or n elements at a time)?

John

Hi John,

How about a little more info - is the a VBA array or are you using VBA to
sort a range in the spreadsheet? What do you mean by 4 elements at a time -
do you want to sort 4 elements internally or do you want to sort a large
collection keeping every four elements together?

1
5
4
3
6
10
8
7


Result 1:
1
3
4
5

6
7
8
10

Bob
Cratchet
1054 West 1st Street
Washington DC
Adam
Smith
43 Glover Blvd
London

Result 2: sorted in blocks of 4 elements based on the first line:
Adam
Smith
43 Glover Blvd
London
Bob
Cratchet
1054 West 1st Street
Washington DC

Thanks,
Shane


"John" wrote in message
...
How do you sort an array 4 elements at a time (or n elements at a time)?

John

Sort the 3 least significant keys first, then sort again on the most
significant.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"John" wrote in message
...
How do you sort an array 4 elements at a time (or n elements at a time)?

John

Huh? Here's what I've tried. I know almost nothing about arrays and
sorting in vb

the array is Round...

dim Round(20) as Integer

For n = 1 To 20 Step 4
Array.Sort(round2 n 4)
Next

I found this somewhere using google. I thought it meant sort from n to n+4.

From the compiler I get "Expected: line number or label or statement or
end statement."


Bob Phillips wrote:
Sort the 3 least significant keys first, then sort again on the most
significant.

Sorry, I thought you were referring to a range array on a worksheet.

How many dimensions does your array have, and what do you think, want, this
to do

Array.Sort(round2 n 4)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"John" wrote in message
...
Huh? Here's what I've tried. I know almost nothing about arrays and
sorting in vb

the array is Round...

dim Round(20) as Integer

For n = 1 To 20 Step 4
Array.Sort(round2 n 4)
Next

I found this somewhere using google. I thought it meant sort from n to
n+4.

From the compiler I get "Expected: line number or label or statement or
end statement."


Bob Phillips wrote:
Sort the 3 least significant keys first, then sort again on the most
significant.

One dimension.

It's the numbers 0 through 20 randomized.

I want to sort the first four, then the next four, then the next four
etc. Might end up looking like:

2 15 18 20 5 9 11 17 1 8 12 14 and so on.

In power Basic the function is "ARRAY SORT Round(n) FOR 4" where n is
the element to start at and 4 is the number of elements to sort. Pretty
simple

Thanks

John


Bob Phillips wrote:
Sorry, I thought you were referring to a range array on a worksheet.

How many dimensions does your array have, and what do you think, want, this
to do

Array.Sort(round2 n 4)

Maybe you could explain a bit more what you need to do. It's not clear what
you're asking.

Tim


"John" wrote in message
...
How do you sort an array 4 elements at a time (or n elements at a time)?

John

If your numbers are in column A starting from the top, then you may
try the following formula in cell B1 and copy it down to B20.
Column B will then contain the sorted numbers.

=SMALL(OFFSET(A$1:A$4,4*INT((ROW()-1)/4),0),INDEX({4,1,2,3},MOD(ROW(),4)+1))

Hope this helps / Lars-Åke



On Sun, 11 May 2008 11:09:21 -0500, John wrote:

One dimension.

It's the numbers 0 through 20 randomized.

I want to sort the first four, then the next four, then the next four
etc. Might end up looking like:

2 15 18 20 5 9 11 17 1 8 12 14 and so on.

In power Basic the function is "ARRAY SORT Round(n) FOR 4" where n is
the element to start at and 4 is the number of elements to sort. Pretty
simple

Thanks

John


Bob Phillips wrote:
Sorry, I thought you were referring to a range array on a worksheet.

How many dimensions does your array have, and what do you think, want, this
to do

Array.Sort(round2 n 4)

Sorry. ...copy it down to B21 would be more correct as you have 21
numbers.


On Sun, 11 May 2008 16:41:03 GMT, Lars-Åke Aspelin
wrote:

If your numbers are in column A starting from the top, then you may
try the following formula in cell B1 and copy it down to B20.
Column B will then contain the sorted numbers.

=SMALL(OFFSET(A$1:A$4,4*INT((ROW()-1)/4),0),INDEX({4,1,2,3},MOD(ROW(),4)+1))

Hope this helps / Lars-Åke



On Sun, 11 May 2008 11:09:21 -0500, John wrote:

One dimension.

It's the numbers 0 through 20 randomized.

I want to sort the first four, then the next four, then the next four
etc. Might end up looking like:

2 15 18 20 5 9 11 17 1 8 12 14 and so on.

In power Basic the function is "ARRAY SORT Round(n) FOR 4" where n is
the element to start at and 4 is the number of elements to sort. Pretty
simple

Thanks

John


Bob Phillips wrote:
Sorry, I thought you were referring to a range array on a worksheet.

How many dimensions does your array have, and what do you think, want, this
to do

Array.Sort(round2 n 4)

Maybe...
'--
Sub SortFourEachTime()
Dim aRound As Variant
Dim N As Long
Dim i As Long
Dim j As Long
Dim Ltemp As Double
Dim rng As Range
Set rng = Range("A1:T1")
aRound = rng.Value

For N = LBound(aRound) To (UBound(aRound, 2) - 3) Step 4
For i = N To N + 2
For j = i + 1 To N + 3
If aRound(1, i) aRound(1, j) Then
Ltemp = aRound(1, i)
aRound(1, i) = aRound(1, j)
aRound(1, j) = Ltemp
End If
Next
Next
Next
rng.Offset(1, 0).Value = aRound
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"John"

wrote in message
One dimension.
It's the numbers 0 through 20 randomized.
I want to sort the first four, then the next four, then the next four
etc. Might end up looking like:

2 15 18 20 5 9 11 17 1 8 12 14 and so on.

In power Basic the function is "ARRAY SORT Round(n) FOR 4" where n is
the element to start at and 4 is the number of elements to sort. Pretty
simple
Thanks
John

Ok I get it.

Try this

Sub TestMySort()
Dim ary As Variant

ary = Array(20, 15, 2, 18, 17, 5, 11, 9, 1, 8, 14, 12)
ary = SortInGroups(ArrayToSort:=ary, NumInGroup:=4)

End Sub

Public Function SortInGroups(ArrayToSort As Variant, Optional NumInGroup As
Long = -1) As Variant
Dim aryToSort As Variant
Dim arySorted As Variant
Dim aryIndex As Long
Dim i As Long

If NumInGroup = -1 Then

SortInGroups = BubbleSort(ArrayToSort)
Else

ReDim arySorted(LBound(ArrayToSort) To UBound(ArrayToSort))
For i = LBound(ArrayToSort) To UBound(ArrayToSort) Step NumInGroup

ReDim aryToSort(1 To 4)
aryToSort(1) = ArrayToSort(LBound(ArrayToSort) + i)
aryToSort(2) = ArrayToSort(LBound(ArrayToSort) + i + 1)
aryToSort(3) = ArrayToSort(LBound(ArrayToSort) + i + 2)
aryToSort(4) = ArrayToSort(LBound(ArrayToSort) + i + 3)

aryToSort = BubbleSort(aryToSort)
arySorted(LBound(ArrayToSort) + i) = aryToSort(1)
arySorted(LBound(ArrayToSort) + i + 1) = aryToSort(2)
arySorted(LBound(ArrayToSort) + i + 2) = aryToSort(3)
arySorted(LBound(ArrayToSort) + i + 3) = aryToSort(4)
Next i
End If
SortInGroups = arySorted
End Function

Private Function BubbleSort(InVal As Variant, Optional Order As String =
"Asc") As Variant
Dim fChanges As Boolean
Dim iElement As Long
Dim iElement2 As Long
Dim temp As Variant
Dim ToSort

ToSort = InVal
Do
fChanges = False
For iElement = LBound(ToSort) To UBound(ToSort) - 1
If ((Order = "Asc" And ToSort(iElement) ToSort(iElement + 1))
Or _
(Order < "Asc" And ToSort(iElement) < ToSort(iElement +
1))) Then
'Swap elements
temp = ToSort(iElement)
ToSort(iElement) = ToSort(iElement + 1)
ToSort(iElement + 1) = temp
fChanges = True
End If
Next iElement
Loop Until Not fChanges

BubbleSort = ToSort
End Function





--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"John" wrote in message
...
One dimension.

It's the numbers 0 through 20 randomized.

I want to sort the first four, then the next four, then the next four etc.
Might end up looking like:

2 15 18 20 5 9 11 17 1 8 12 14 and so on.

In power Basic the function is "ARRAY SORT Round(n) FOR 4" where n is the
element to start at and 4 is the number of elements to sort. Pretty simple

Thanks

John


Bob Phillips wrote:
Sorry, I thought you were referring to a range array on a worksheet.

How many dimensions does your array have, and what do you think, want,
this to do

Array.Sort(round2 n 4)

This is frustrating. Am I to understand that neither VB nor excel has an
included sort function for arrays? You have to write your own? Like
the bubble sort you included below?. That is hard to believe and seems
pretty primitive. If nothing else pops up I can use your example. Thanks

JOhn

Bob Phillips wrote:
Ok I get it.

Try this

Sub TestMySort()
Dim ary As Variant

ary = Array(20, 15, 2, 18, 17, 5, 11, 9, 1, 8, 14, 12)
ary = SortInGroups(ArrayToSort:=ary, NumInGroup:=4)

End Sub

Public Function SortInGroups(ArrayToSort As Variant, Optional NumInGroup As
Long = -1) As Variant
Dim aryToSort As Variant
Dim arySorted As Variant
Dim aryIndex As Long
Dim i As Long

If NumInGroup = -1 Then

SortInGroups = BubbleSort(ArrayToSort)
Else

ReDim arySorted(LBound(ArrayToSort) To UBound(ArrayToSort))
For i = LBound(ArrayToSort) To UBound(ArrayToSort) Step NumInGroup

ReDim aryToSort(1 To 4)
aryToSort(1) = ArrayToSort(LBound(ArrayToSort) + i)
aryToSort(2) = ArrayToSort(LBound(ArrayToSort) + i + 1)
aryToSort(3) = ArrayToSort(LBound(ArrayToSort) + i + 2)
aryToSort(4) = ArrayToSort(LBound(ArrayToSort) + i + 3)

aryToSort = BubbleSort(aryToSort)
arySorted(LBound(ArrayToSort) + i) = aryToSort(1)
arySorted(LBound(ArrayToSort) + i + 1) = aryToSort(2)
arySorted(LBound(ArrayToSort) + i + 2) = aryToSort(3)
arySorted(LBound(ArrayToSort) + i + 3) = aryToSort(4)
Next i
End If
SortInGroups = arySorted
End Function

Private Function BubbleSort(InVal As Variant, Optional Order As String =
"Asc") As Variant
Dim fChanges As Boolean
Dim iElement As Long
Dim iElement2 As Long
Dim temp As Variant
Dim ToSort

ToSort = InVal
Do
fChanges = False
For iElement = LBound(ToSort) To UBound(ToSort) - 1
If ((Order = "Asc" And ToSort(iElement) ToSort(iElement + 1))
Or _
(Order < "Asc" And ToSort(iElement) < ToSort(iElement +
1))) Then
'Swap elements
temp = ToSort(iElement)
ToSort(iElement) = ToSort(iElement + 1)
ToSort(iElement + 1) = temp
fChanges = True
End If
Next iElement
Loop Until Not fChanges

BubbleSort = ToSort
End Function

Come on, get real. You are asking for a sort that takes groups of 4 elements
in an array and sorts them in isolation to the rest of the array.

You tell me ANY language that provides such a facility.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"John" wrote in message
...
This is frustrating. Am I to understand that neither VB nor excel has an
included sort function for arrays? You have to write your own? Like the
bubble sort you included below?. That is hard to believe and seems pretty
primitive. If nothing else pops up I can use your example. Thanks

JOhn

Bob Phillips wrote:
Ok I get it.

Try this

Sub TestMySort()
Dim ary As Variant

ary = Array(20, 15, 2, 18, 17, 5, 11, 9, 1, 8, 14, 12)
ary = SortInGroups(ArrayToSort:=ary, NumInGroup:=4)

End Sub

Public Function SortInGroups(ArrayToSort As Variant, Optional NumInGroup
As Long = -1) As Variant
Dim aryToSort As Variant
Dim arySorted As Variant
Dim aryIndex As Long
Dim i As Long

If NumInGroup = -1 Then

SortInGroups = BubbleSort(ArrayToSort)
Else

ReDim arySorted(LBound(ArrayToSort) To UBound(ArrayToSort))
For i = LBound(ArrayToSort) To UBound(ArrayToSort) Step
NumInGroup

ReDim aryToSort(1 To 4)
aryToSort(1) = ArrayToSort(LBound(ArrayToSort) + i)
aryToSort(2) = ArrayToSort(LBound(ArrayToSort) + i + 1)
aryToSort(3) = ArrayToSort(LBound(ArrayToSort) + i + 2)
aryToSort(4) = ArrayToSort(LBound(ArrayToSort) + i + 3)

aryToSort = BubbleSort(aryToSort)
arySorted(LBound(ArrayToSort) + i) = aryToSort(1)
arySorted(LBound(ArrayToSort) + i + 1) = aryToSort(2)
arySorted(LBound(ArrayToSort) + i + 2) = aryToSort(3)
arySorted(LBound(ArrayToSort) + i + 3) = aryToSort(4)
Next i
End If
SortInGroups = arySorted
End Function

Private Function BubbleSort(InVal As Variant, Optional Order As String =
"Asc") As Variant
Dim fChanges As Boolean
Dim iElement As Long
Dim iElement2 As Long
Dim temp As Variant
Dim ToSort

ToSort = InVal
Do
fChanges = False
For iElement = LBound(ToSort) To UBound(ToSort) - 1
If ((Order = "Asc" And ToSort(iElement) ToSort(iElement +
1)) Or _
(Order < "Asc" And ToSort(iElement) < ToSort(iElement +
1))) Then
'Swap elements
temp = ToSort(iElement)
ToSort(iElement) = ToSort(iElement + 1)
ToSort(iElement + 1) = temp
fChanges = True
End If
Next iElement
Loop Until Not fChanges

BubbleSort = ToSort
End Function

Power Basic
"ARRAY SORT Array(n) FOR m." Sorts starting at item n and sorts m cells
so my problem is easy

For n = 1 to 20 step 4
ARRAY SORT Array(n) for 4
Next

I think almost all basics have a function like this except excel vb. I
believe excel substitutes cells for arrays most the time so doesn't need
robust array handlers. It also doesn't have a scan or find function for
arrays but it has one for cells.

It occurred to me that I can just open a new sheet and use it like an
array. VB excel has the tools to sort columns of cells.

In this case I wrote a simple sort sub since it's only 4 items and call
it when I need it.

John



Bob Phillips wrote:
Come on, get real. You are asking for a sort that takes groups of 4 elements
in an array and sorts them in isolation to the rest of the array.

You tell me ANY language that provides such a facility.

Its an array of 20 integers. I want to sort them 4 at a time. Like your
result 1 below.
John


Shane Devenshire wrote:
Hi John,

How about a little more info - is the a VBA array or are you using VBA
to sort a range in the spreadsheet? What do you mean by 4 elements at a
time - do you want to sort 4 elements internally or do you want to sort
a large collection keeping every four elements together?

1
5
4
3
6
10
8
7


Result 1:
1
3
4
5

6
7
8
10

Bob
Cratchet
1054 West 1st Street
Washington DC
Adam
Smith
43 Glover Blvd
London

Result 2: sorted in blocks of 4 elements based on the first line:
Adam
Smith
43 Glover Blvd
London
Bob
Cratchet
1054 West 1st Street
Washington DC

Thanks,
Shane


"John" wrote in message
...
How do you sort an array 4 elements at a time (or n elements at a time)?

John