Sort array with 2 dimensions?
 

I have an array:


Code:
--------------------

ReDim Preserve AllCustomers(1 To 21, 1 To 7)

--------------------


The array contains the following data:
(Below, contains data for customer 1.)
-----------------------------------------------------
AllCustomers(1 , 1 ) contains CustomerNo
AllCustomers(1 , 2 ) contains Date
AllCustomers(1 , 3 ) contains Address
.......
........
AllCustomers(1 , 7 ) contains Duration
etc
-----------------------------------------------------

(While, belowcontains data for customer 2.)
-----------------------------------------------------
AllCustomers(2 , 1 ) contains CustomerNo
AllCustomers(2 , 2 ) contains Date
AllCustomers(2 , 3 ) contains Cost
.......
........
AllCustomers(1 , 7 ) contains Duration
etc
-----------------------------------------------------




I want to sort them by CustomerNo which is a string in the format
'M*****', where * is a numeric value.

With a normal, single dimension array I would use Right() on CustomerNo
and then sort using these values. However, I want to sort by the
CustomerNo (which is in the 2nd dimension), and want all the values
that correspond with the customer,
to move with the CustomerNo.

e.g.

unsorted
------------
M00021, 13/02/2005, 80.00,....,....,2
to
M00001, 12/02/2005, 40.00,....,....,1


when sorted
----------------
M00001, 12/02/2005, 40.00,....,....,1
to

M00021, 13/02/2005, 80.00,....,....,2

Any ideas on how to sort this would be greatly appreciated. Just never
used more than one dimension before.


--
eclipse
------------------------------------------------------------------------
eclipse's Profile: http://www.excelforum.com/member.php...o&userid=28372
View this thread: http://www.excelforum.com/showthread...hreadid=479596

Sort array with 2 dimensions?
 

e,
Of course you could put the array on a spreadsheet and sort it there.
If that doesn't appeal, then this code may work for you.
I loaded the array from a spreadsheet and then put the sorted array back
on the spreadsheet in order to verify the code.
The code could be generic, if one determined the lower bound
of the second dimension. ( I assumed 1).
Jim Cone
San Francisco, USA

'------------------------------
Function SortWideArray(ByRef arrWide As Variant)
'The variant argument which contains the array
'is returned to the calling sub with the array sorted.
'The sorting is by the first dimension only.
'Jim Cone - San Francisco, USA - October 2005
Dim arrTemp1() As Variant
Dim arrTemp2() As Variant
Dim lngSize1 As Long
Dim lngSize2 As Long
Dim lngStart As Long
Dim i As Long
Dim j As Long
Dim lngAcross As Long

lngSize1 = UBound(arrWide, 1)
lngSize2 = UBound(arrWide, 2)
lngStart = LBound(arrWide, 1)
ReDim arrTemp1(1 To 1, 1 To lngSize2)
ReDim arrTemp2(1 To 1, 1 To lngSize2)

For i = lngStart To lngSize1 - 1
For j = (i + 1) To lngSize1
If arrWide(i, 1) arrWide(j, 1) Then
For lngAcross = 1 To lngSize2
arrTemp1(1, lngAcross) = arrWide(i, lngAcross)
arrTemp2(1, lngAcross) = arrWide(j, lngAcross)
Next 'lngAcross
For lngAcross = 1 To lngSize2
arrWide(i, lngAcross) = arrTemp2(1, lngAcross)
arrWide(j, lngAcross) = arrTemp1(1, lngAcross)
Next 'lngAcross
End If
Next 'j
Next 'i
End Function

'Call Function
Sub GetTheArraySorted()
Dim varRange As Variant
varRange = Range("B5:H25").Value
Call SortWideArray(varRange)
Range("J5:P25").Value = varRange
End Sub
'---------------------------------------------



"eclipse" wrote...
I have an array:
Code:
--------------------
ReDim Preserve AllCustomers(1 To 21, 1 To 7)
--------------------
The array contains the following data:
(Below, contains data for customer 1.)
-----------------------------------------------------
AllCustomers(1 , 1 ) contains CustomerNo
AllCustomers(1 , 2 ) contains Date
AllCustomers(1 , 3 ) contains Address
.......
........
AllCustomers(1 , 7 ) contains Duration
etc
-----------------------------------------------------
(While, belowcontains data for customer 2.)
-----------------------------------------------------
AllCustomers(2 , 1 ) contains CustomerNo
AllCustomers(2 , 2 ) contains Date
AllCustomers(2 , 3 ) contains Cost
.......
........
AllCustomers(1 , 7 ) contains Duration
etc
-----------------------------------------------------

I want to sort them by CustomerNo which is a string in the format
'M*****', where * is a numeric value.
With a normal, single dimension array I would use Right() on CustomerNo
and then sort using these values. However, I want to sort by the
CustomerNo (which is in the 2nd dimension), and want all the values
that correspond with the customer,
to move with the CustomerNo.
e.g.
unsorted
------------
M00021, 13/02/2005, 80.00,....,....,2
to
M00001, 12/02/2005, 40.00,....,....,1
when sorted
----------------
M00001, 12/02/2005, 40.00,....,....,1
to
M00021, 13/02/2005, 80.00,....,....,2

Any ideas on how to sort this would be greatly appreciated. Just never
used more than one dimension before.
--
eclipse

Sort array with 2 dimensions?
 

Will give it a try, and get back to you, thanks for the help. :)


--
eclipse
------------------------------------------------------------------------
eclipse's Profile: http://www.excelforum.com/member.php...o&userid=28372
View this thread: http://www.excelforum.com/showthread...hreadid=479596

Sort array with 2 dimensions?
 

Code:
--------------------


If Right((arrWide(i, 1)), 5) Right((arrWide(j, 1)), 5) Then


--------------------


Changed the if above so that it just compares the last five digits and
it works a treat,
Thanks for showing me how lbound and ubound work with multi dimension
arrays.


--
eclipse
------------------------------------------------------------------------
eclipse's Profile: http://www.excelforum.com/member.php...o&userid=28372
View this thread: http://www.excelforum.com/showthread...hreadid=479596

Sort array with 2 dimensions?
 

e,
You are welcome. Appreciate getting the feedback.
Jim Cone
San Francisco, USA


"eclipse" wrote...
Code:
--------------------
If Right((arrWide(i, 1)), 5) Right((arrWide(j, 1)), 5) Then
--------------------
Changed the if above so that it just compares the last five digits and
it works a treat,
Thanks for showing me how lbound and ubound work with multi dimension
arrays.
eclipse