Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Help
Hi all, I have created a 2-d array that stored info; Code ------------------- j = i Do Cells(j, 3).Select If ActiveCell.Value < "" Then maxArray = maxArray + 1 ReDim Preserve Arry(1 To 4, 1 To maxArray) Arry(1, maxArray) = ActiveCell.Value Arry(2, maxArray) = ActiveCell.Offset(0, 1).Value Arry(3, maxArray) = Right(ActiveCell.Offset(0, 2).Value, 6) Arry(4, maxArray) = Val(ActiveCell.Offset(0, 8).Value / 1000) End If ------------------- My question to you is.....if Arry(1, maxArray) and Arry(2, maxArray and Arry(3, maxArray) are the same then I want to group them and tota the Arry(4, maxArray) value. Would the array first have to be sorted first and then grouped? An advice welcome! TI -- gti_jober ----------------------------------------------------------------------- gti_jobert's Profile: http://www.excelforum.com/member.php...fo&userid=3063 View this thread: http://www.excelforum.com/showthread.php?threadid=52305 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Help
Do you mean get a running total:
tot = 0 j = i Do Cells(j, 3).Select If ActiveCell.Value < "" Then maxArray = maxArray + 1 ReDim Preserve Arry(1 To 4, 1 To maxArray) Arry(1, maxArray) = ActiveCell.Value Arry(2, maxArray) = ActiveCell.Offset(0, 1).Value Arry(3, maxArray) = Right(ActiveCell.Offset(0, 2).Value, 6) Arry(4, maxArray) = Val(ActiveCell.Offset(0, 8).Value / 1000) if arry(1,maxArray) = arry(2,maxArray) and _ arry(2,maxArray) = arry(3,maxArray) then tot = tot + arry(4,maxArray) end if End If -- Regards, Tom Ogilvy "gti_jobert" wrote: Hi all, I have created a 2-d array that stored info; Code: -------------------- j = i Do Cells(j, 3).Select If ActiveCell.Value < "" Then maxArray = maxArray + 1 ReDim Preserve Arry(1 To 4, 1 To maxArray) Arry(1, maxArray) = ActiveCell.Value Arry(2, maxArray) = ActiveCell.Offset(0, 1).Value Arry(3, maxArray) = Right(ActiveCell.Offset(0, 2).Value, 6) Arry(4, maxArray) = Val(ActiveCell.Offset(0, 8).Value / 1000) End If -------------------- My question to you is.....if Arry(1, maxArray) and Arry(2, maxArray) and Arry(3, maxArray) are the same then I want to group them and total the Arry(4, maxArray) value. Would the array first have to be sorted first and then grouped? Any advice welcome! TIA -- gti_jobert ------------------------------------------------------------------------ gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634 View this thread: http://www.excelforum.com/showthread...hreadid=523057 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Help
Hi, no I dont mean a running total, for example, if the following data wa stored in the array; WB01 12345 987 6 WB01 12345 987 90 WB02 84638 247 9 I want to be able to group them to produce the following; WB01 12345 987 96 WB02 84638 247 9 with the last entry totaled if there are duplicates. Thanks again -- gti_jober ----------------------------------------------------------------------- gti_jobert's Profile: http://www.excelforum.com/member.php...fo&userid=3063 View this thread: http://www.excelforum.com/showthread.php?threadid=52305 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Help
I'm just trying to sort the Array first, does anyone know how to sort 2-d array. Examples on the web are 1-d : -- gti_jober ----------------------------------------------------------------------- gti_jobert's Profile: http://www.excelforum.com/member.php...fo&userid=3063 View this thread: http://www.excelforum.com/showthread.php?threadid=52305 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Help
You don't just want a method to sort a 2-D array (which I have posted in the
past), you also need a method to sort a 2-D array on multiple Keys. An alternative would be 2-D on a single key but using a method which does a stable/static sort. You could then sort lowest to most significance successively - much like you can do with Excel's sort method. Unfortunately, my method was quicksort which isn't stable/static. It might be easier to drop your array in a worksheet and use the subtotal function under the data menu. -- Regards, Tom Ogilvy "gti_jobert" wrote: I'm just trying to sort the Array first, does anyone know how to sort a 2-d array. Examples on the web are 1-d :( -- gti_jobert ------------------------------------------------------------------------ gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634 View this thread: http://www.excelforum.com/showthread...hreadid=523057 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex conditional summing - array COUNT works, array SUM gives#VALUE | Excel Worksheet Functions | |||
Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming |