Group cells without the totals (conditional grouping)

[Migaspt]

Hello!

I'm trying to write the code to run a macro on VB to group rows until the totals.


Example:

[TABLE="width: 320"]
<tbody>[TR]
[TD]TITLE 1[/TD]
[TD]TITLE 2[/TD]
[TD]TITLE 3[/TD]
[TD]TITLE 4[/TD]
[TD]%[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]X1[/TD]
[TD]X11[/TD]
[TD][/TD]
[TD="align: right"]98[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]X2[/TD]
[TD]X21[/TD]
[TD][/TD]
[TD="align: right"]87[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]X3[/TD]
[TD]X31[/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]X1[/TD]
[TD]X12[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]X3[/TD]
[TD]X32[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]X TOTAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]53.75[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Y1[/TD]
[TD]Y11[/TD]
[TD][/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Y1[/TD]
[TD]Y12[/TD]
[TD][/TD]
[TD="align: right"]98[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Y2[/TD]
[TD]Y21[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Y TOTAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]58.44[/TD]
[/TR]
[TR]
[TD]…[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]56.35[/TD]
[/TR]
</tbody>[/TABLE]

What I need is to group the Xs and Ys until the totals. The code should stop the groupings when there is the "Grand Total" in column A.
-Some groups can have only one cell to group.
-Grouping should start at the 14th row

(Another way to write it could be to group until the blank cells in column B?)


Can you help me with this?

Thank you very much in advance!

 

[oldbrewer]

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TITLE 1[/TD]
[TD]TITLE 2[/TD]
[TD]TITLE 3[/TD]
[TD]TITLE 4[/TD]
[TD]%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]X[/TD]
[TD]X1[/TD]
[TD]X11[/TD]
[TD][/TD]
[TD="align: right"]98[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]X[/TD]
[TD]X2[/TD]
[TD]X21[/TD]
[TD][/TD]
[TD="align: right"]87[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]X[/TD]
[TD]X3[/TD]
[TD]X31[/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]X[/TD]
[TD]X1[/TD]
[TD]X12[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]X[/TD]
[TD]X3[/TD]
[TD]X32[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]X TOTAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]53.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Y[/TD]
[TD]Y1[/TD]
[TD]Y11[/TD]
[TD][/TD]
[TD="align: right"]68[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Y[/TD]
[TD]Y1[/TD]
[TD]Y12[/TD]
[TD][/TD]
[TD="align: right"]98[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Y[/TD]
[TD]Y2[/TD]
[TD]Y21[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Y TOTAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]58.44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]56.35[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]the pivot table is in a fixed starting location[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]in this case cell B2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]so we can start pulling data from B3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]we do not pull a line if it contains the text "total"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]so if B cells are not blank we pull the C,D,E,F cells[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]########[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]>>>>>[/TD]
[TD]X[/TD]
[TD]X1[/TD]
[TD]X11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]X[/TD]
[TD]X2[/TD]
[TD]X21[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]87[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]X[/TD]
[TD]X3[/TD]
[TD]X31[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]X[/TD]
[TD]X1[/TD]
[TD]X12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]X[/TD]
[TD]X3[/TD]
[TD]X32[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Y[/TD]
[TD]Y1[/TD]
[TD]Y11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]68[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Y[/TD]
[TD]Y1[/TD]
[TD]Y12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Y[/TD]
[TD]Y2[/TD]
[TD]Y21[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]formula in cell marked >>>>[/TD]
[TD](b25)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]=IF(B3="","",IF(ISERROR(SEARCH("total",B3)),B3,""))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]formula in cell marked ####[/TD]
[TD](c25)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]=IF(B25="","",C3)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[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]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Migaspt]

Shall I use those formulas in Virtual Basic?

 

[oldbrewer]

the syntax is not the same you will need a VBA manual

if it must be VBA it is probably easier to loop through the pivot table printing only lines without total inthe first column