obscure array sort

[John]

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

John

 

[Shane Devenshire]

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

 

[Bob Phillips]

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]

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]

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]

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

 

[Tim Williams]

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

Tim

 

[Lars-Åke Aspelin]

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

 

[Lars-Åke Aspelin]

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

 

[Jim Cone]

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"
<[email protected]>
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]

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]

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]

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]

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

 

[John]

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