Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array... Sort of... | Excel Worksheet Functions | |||
Array function, two dimensions?? and worksheet arrays | Excel Programming | |||
Array function, two dimensions?? | Excel Programming | |||
Checking existence of array dimensions | Excel Programming | |||
Checking Number of Dimensions In Array | Excel Programming |