obscure array sort

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John - 11 May 2008 15:14 GMT

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

John

Reply to this Message

Shane Devenshire - 11 May 2008 16:09 GMT

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

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

Reply to this Message

John - 13 May 2008 04:15 GMT

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

> Hi John,
>
[quoted text clipped - 48 lines]
>>
>> John

Reply to this Message

Bob Phillips - 11 May 2008 16:14 GMT

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

Signature

HTH

Bob

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

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

Reply to this Message

John - 11 May 2008 16:34 GMT

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."

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

Reply to this Message

Bob Phillips - 11 May 2008 16:59 GMT

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)

Signature

HTH

Bob

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

> Huh? Here's what I've tried. I know almost nothing about arrays and
> sorting in vb
[quoted text clipped - 15 lines]
>> Sort the 3 least significant keys first, then sort again on the most
>> significant.

Reply to this Message

John - 11 May 2008 17:09 GMT

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

> 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)

Reply to this Message

Lars-Åke Aspelin - 11 May 2008 17:41 GMT

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

>One dimension.
>
[quoted text clipped - 19 lines]
>>
>> Array.Sort(round2 n 4)

Reply to this Message

Lars-Åke Aspelin - 11 May 2008 17:42 GMT

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

>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.
[quoted text clipped - 27 lines]
>>>
>>> Array.Sort(round2 n 4)

Reply to this Message

Jim Cone - 11 May 2008 18:08 GMT

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

Signature

Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"John"
<john6528@comcast.com>
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

Reply to this Message

Bob Phillips - 11 May 2008 18:11 GMT

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

Signature

HTH

Bob

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

> One dimension.
>
[quoted text clipped - 18 lines]
>>
>> Array.Sort(round2 n 4)

Reply to this Message

John - 12 May 2008 00:17 GMT

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

> Ok I get it.
>
[quoted text clipped - 66 lines]
> BubbleSort = ToSort
> End Function

Reply to this Message

Bob Phillips - 12 May 2008 10:13 GMT

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.

Signature

HTH

Bob

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

> 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
[quoted text clipped - 74 lines]
>> BubbleSort = ToSort
>> End Function

Reply to this Message

John - 12 May 2008 14:42 GMT

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

> 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.

Reply to this Message

Tim Williams - 11 May 2008 17:14 GMT

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

Tim

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

Reply to this Message

Rate this thread: