how to see the totals of amounts in various combinations?

[sivanandan dinesh]

how to see the totals of amounts in various combinations ?
<table width="113" border="0" cellpadding="0" cellspacing="0"><col style="width: 85pt;" width="113"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 85pt;" width="113" height="20">21,372.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">29,800.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">30,455.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">59,771.50 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">30,000.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">37,500.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">60,900.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">28,500.00 </td> </tr> </tbody></table>

 

[MickG]

Try this:-
Paste you Data in Range(C1: C8).
Format as "General".
Run code for Combinations in "A" and Sum Of in "B".
Change Range in "Rng" Variable for more Numbers.

[COLOR="Navy"]Sub[/COLOR] MG22Mar09
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray()
[COLOR="Navy"]Dim[/COLOR] Nums [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] oLets [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] temp1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lps [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] St [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] temp2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Columns("A:B").ClearContents
[COLOR="Navy"]Set[/COLOR] rng = Range("C1:C8")
oLets = Application.Transpose(rng.Value)
Nums = Application.Transpose(Evaluate("row(" & rng.Address & ")-" & rng.Row & "+1"))
c = 0
    [COLOR="Navy"]For[/COLOR] n = 1 To UBound(Nums)
       ReDim Preserve Ray(c)
        Ray(c) = Nums(n)
        c = c + 1
        Cells(c, 1) = oLets(n)
       [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] Lps = 2 To UBound(Nums)
St = IIf(UBound(Ray) > UBound(Nums), temp, 0)
    temp = UBound(Ray) + 1
    txt = txt & UBound(Ray) & ","
    [COLOR="Navy"]For[/COLOR] R = St To UBound(Ray)
        For p = Right(Ray(R), 1) + 1 To UBound(Nums) '[COLOR="Green"][B]+ 1[/B][/COLOR]
            ReDim Preserve Ray(c)
            Ray(c) = Ray(R) & "," & p
            c = c + 1
           temp1 = Ray(R) & "," & p
            Sp = Split(temp1, ",")
        [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
            Cells(c, 1) = Cells(c, 1) & oLets(Sp(n)) & ","
        [COLOR="Navy"]Next[/COLOR] n
           temp2 = Left(Cells(c, 1), Len(Cells(c, 1)) - 1)
           Cells(c, 2) = Evaluate(Replace(temp2, ",", "+"))
        [COLOR="Navy"]Next[/COLOR] p
     [COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]Next[/COLOR] Lps
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick