# Summation in 2D Array

## Recommended Posts

Array ABC has 0 - 6 colums, multiple rows.
Attempting to sum a value in column 4 by changes in values in column 5 and show that sum in column 6 where the break happens.

Col0            Col1       Col2   Col3   Col4    Col5       Col6
Row0                                              \$4.50 dog

Row1                                               \$12.49 dog

Row2                                                 \$100 dog     \$116.99  (<< this would be the total of  dog)

Row3                                                \$2.75 bird

>
>
>
>
>

RowN                                                \$13.25 bird   \$16.00 (<< this would be the total of bird)

```for \$i = 0 to UBound(ABC, 1) -1
\$j = 5
\$m = (1 +\$i)
if ABC[\$i][\$j] = ABC[\$m][\$j]Then   ;this is where the error msg shows up
ABC[\$m][6] = ABC[\$i][4] + ABC[\$m] [4]
EndIf
Next```

So the totalling takes place when the element in column 5 changes.  Here is what I have, but I am getting an error about the subscript and can't figure it out.

Error:
Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

##### Share on other sites

Is the array always sorted at column 5?

Br,

UEZ

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

##### Share on other sites

With a non-sorted array, you can do something like this :

```#Include <Array.au3>

Local \$aValues[7][3] =  [ [ 10, "dog"], [11, "cat"], [ 2.5, "bird"], [3, "bird"], [ 12, "dog"], [7, "cat"], [32, "dog"] ]
Local \$aKeys[1] = [0]

For \$i =  0 To UBound(\$aValues) - 1
Assign("INDEX_" & \$aValues[\$i][1], \$i )
If IsDeclared("ITEM_" & \$aValues[\$i][1]) Then
Assign("ITEM_" & \$aValues[\$i][1], Eval("ITEM_" & \$aValues[\$i][1]) + \$aValues[\$i][0] )
Else
Redim \$aKeys[ UBound(\$aKeys) + 1]
\$aKeys[UBound(\$aKeys) - 1] = \$aValues[\$i][1]
\$aKeys[0] += 1
Assign("ITEM_" & \$aValues[\$i][1], \$aValues[\$i][0] )
EndIf
Next

For \$i = 1 To \$aKeys[0]
\$aValues[ Eval("INDEX_" & \$aKeys[\$i]) ][2] = Eval("ITEM_" & \$aKeys[\$i])
Next

_ArrayDisplay(\$aValues)```

##### Share on other sites

This statement   \$m = (1 +\$i)
will result in an index exceeding by 1 the last element of array at end of loop.

maybe you could try like this:

```For \$i = 1 To UBound(ABC, 1) - 1 ; loop start at 1
\$j = 5
\$m = (\$i - 1) ; instead of  \$m = (1 +\$i)
If ABC[\$i][\$j] = ABC[\$m][\$j] Then ;this is where the error msg shows up

ABC[\$i][6] = ABC[\$i][4] + ABC[\$m][4] ; instead of ABC[\$m][6] = ABC[\$i][4] + ABC[\$m] [4]
EndIf
Next```

not tested

Edited by PincoPanco

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

##### Share on other sites

Yes the array is always sorted by  col5 in order to group the data.

I see where I went wrong as well :

for \$i = 0 to UBound(ABC, 1) -1 <<<<<<<< make that a -2 and I think it works.

I see also that it won't sum the categories in col5, but looks like it will add two.

Basically looking to 1.) with the sorted array, sum the categories in col5 by category.

##### Share on other sites
jguinch

yours works well for unsorted.  I need to use the sorted array approach.  Is your solution expandable to a sorted array?

I'm new at this so I don't want to go down dead ends. Still learning.

##### Share on other sites

sure will:

```#Include <Array.au3>

Global Enum \$iValues_Col0, \$iValues_Col1, \$iValues_Col2, \$iValues_Price, \$iValues_Type, \$iValues_Sum, \$iValues_ColUBound
Local \$aValues[7][\$iValues_ColUBound] =  [ ["","","",10,"dog"], [ "","","",11,"cat"], [ "","","",2.5,"bird"], [ "","","",3,"bird"], [ "","","", 12,"dog"], [ "","","",7,"cat"], [ "","","",32,"dog"] ]
_ArraySort(\$aValues,0,0,0,\$iValues_Type)
_ArrayDisplay(\$aValues)
Local \$aKeys[1] = [0]

For \$i =  0 To UBound(\$aValues) - 1
Assign("INDEX_" & \$aValues[\$i][\$iValues_Type], \$i )
If IsDeclared("ITEM_" & \$aValues[\$i][\$iValues_Type]) Then
Assign("ITEM_" & \$aValues[\$i][\$iValues_Type], Eval("ITEM_" & \$aValues[\$i][\$iValues_Type]) + \$aValues[\$i][\$iValues_Price] )
Else
Redim \$aKeys[ UBound(\$aKeys) + 1]
\$aKeys[UBound(\$aKeys) - 1] = \$aValues[\$i][\$iValues_Type]
\$aKeys[0] += 1
Assign("ITEM_" & \$aValues[\$i][\$iValues_Type], \$aValues[\$i][\$iValues_Price] )
EndIf
Next

For \$i = 1 To \$aKeys[0]
\$aValues[ Eval("INDEX_" & \$aKeys[\$i])][\$iValues_Sum] = Eval("ITEM_" & \$aKeys[\$i])
Next

_ArrayDisplay(\$aValues)```

output:

[0]||||2.5|bird|
[1]||||3|bird|5.5
[2]||||7|cat|
[3]||||11|cat|18
[4]||||32|dog|
[5]||||10|dog|
[6]||||12|dog|54

Edited by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

##### Share on other sites

another approach

```#include <Array.au3>
Global \$sum
Local \$aValues[7][6] = [["", "", "", 10, "dog"],["", "", "", 11, "bird"],["", "", "", 2.5, "bird"],["", "", "", 3, "bird"],["", "", "", 12, "dog"],["", "", "", 7, "cat"],["", "", "", 32, "dog"]]
_ArraySort(\$aValues, 0, 0, 0, 4)
_ArrayDisplay(\$aValues)
Local \$aKeys = _ArrayUnique(\$aValues, 5)
For \$i = 1 To \$aKeys[0]
Local \$filtered = _ArrayFindAll(\$aValues, \$aKeys[\$i], 0, 0, 0, 0, 4)
\$sum = 0
For \$x = 0 To UBound(\$filtered) - 1
\$sum += \$aValues[\$filtered[\$x]][3]
Next
\$aValues[_ArraySearch(\$aValues, \$aKeys[\$i], 0, 0, 0, 0, 0, 4)][5] = \$sum
Next
_ArrayDisplay(\$aValues)```

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

##### Share on other sites

PincoPanco

Nice!  I'm trying out both.

thanks again.

## Create an account

Register a new account

×

• Wiki

• Back

• #### Beta

• Git
• FAQ
• Our Picks
×
• Create New...