Dynamic Array Summation

[akuzucu]

Hi all,

I am looking forward to see your suggestions for my excel problem:

I have consecutive arrays all of which are in descending order in Column A. Each array starts from a random number and always ends up with 1. Each array is seperated from other by random variables. For each number in array there is a corresponding value in column B. What I want to do is to find out the sum of numbers in column B for each consecutive set.

To give an example: I want to find out and show the sum for row 1 to 4 (A have consecutive numbers) on B1 which is 11. Then, ignore row 5. Then starts another consecutive set. Find its sum and show the result on row 6 which is 27. The issue is I do not know the length of each array, it is variable.

----A-- B
1 -- 7 - 5
2 -- 6-- 1
3-- 5--- 2
4 --4 -- 3
5 --1 --6
6 --4 --9
7 --3 --4
8 --2 --6
9 --1 --8
10 -7 --2
11 -3 --1
12 -2 --0
13 -1 --5

Thanks!

 

[MickG]

Try this:-
Results in "C".

[COLOR="Navy"]Sub[/COLOR] MG24Jan52
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Twn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    n = n + 1
        Twn = Dn + n
    [COLOR="Navy"]If[/COLOR] Not .Exists(Twn) [COLOR="Navy"]Then[/COLOR]
        .Add Twn, Array(Dn, Dn.Offset(, 1))
    [COLOR="Navy"]Else[/COLOR]
        Q = .Item(Twn)
        Q(1) = Q(1) + Dn.Offset(, 1)
        Q(0).Offset(, 2).Value = Q(1)
        .Item(Twn) = Q
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[akuzucu]

That works!

Many thanks!!