VBA Group total

[yirga]

Please see the following and help me how to get the result on Sheet 2 in Macro



[TABLE="width: 434"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD][COLOR=#0000ff]Sheet1[/COLOR][/TD]
[TD](input)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Price1[/TD]
[TD]Price2[/TD]
[TD]Price3[/TD]
[/TR]
[TR]
[TD]Level1[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lemon-Yellow[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Banana[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lemon-Green[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Level2[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lemon-Yellow[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Banana[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lemon-Green[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Level3[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lemon-Yellow[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Banana[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lemon-Green[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][COLOR=#0000ff]Sheet2[/COLOR][/TD]
[TD]expected (Output)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Level1[/TD]
[TD]total-nonLemons[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]total-justLemons[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Level2[/TD]
[TD]total-nonLemons[/TD]
[TD][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]total-justLemons[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Level3[/TD]
[TD]total-nonLemons[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]total-justLemons[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]

 

[SteveO59L]

why cant you use countif()?

 

[MickG]

Try:=
Data in Starts "A2 (with word "Level1").
Results start Column "K"

[COLOR="Navy"]Sub[/COLOR] MG14May10
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Temp [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("B" & Rows.Count).End(xlUp).Row
    [COLOR="Navy"]Set[/COLOR] Rng = Range("A2").Resize(Lst).SpecialCells(xlCellTypeBlanks)
        ReDim Ray(1 To Rng.Areas.Count * 2 + 1, 1 To 6)
            Ray(1, 4) = "Price 1": Ray(1, 5) = "Price 2": Ray(1, 6) = "Price 3"
c = 2
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    Ray(c, 1) = Dn(1).Offset(-1)
    Ray(c, 2) = "Total-NonLemons"
    Ray(c + 1, 2) = "Total-JustLemons"
    [COLOR="Navy"]Set[/COLOR] nRng = Dn.Offset(-1).Resize(Dn.Count + 1)
       
       [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] nRng
            [COLOR="Navy"]For[/COLOR] Ac = 4 To 6
              [COLOR="Navy"]If[/COLOR] InStr(R.Offset(, 1), "Lemon") > 0 [COLOR="Navy"]Then[/COLOR]
                Ray(c + 1, Ac) = Ray(c + 1, Ac) + R(, Ac)
              [COLOR="Navy"]Else[/COLOR]
                 Ray(c, Ac) = Ray(c, Ac) + R(, Ac)
              [COLOR="Navy"]End[/COLOR] If
           [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]Next[/COLOR] R


c = c + 2
[COLOR="Navy"]Next[/COLOR] Dn
Range("K1").Resize(c - 1, 6).Value = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick