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]

when you look in excel help group and ungroup refer to pivot table items

I cannot see how grouping a few cells together is a benefit - somebody else please step in to help.

In the mean time forget your actual data, makeup a pretend table of data, tell us how you want to analyse it and provide a desired finished table, so we can see what you want in a very simple way.

 

[Migaspt]

I need to group rows to this file.

What I asked was if I can use code to run a macro that automaticly groups the rows I need grouped.

How can I upload the table here?

Is this code possible?


So far I got this through the Macro Recorder:

Sub groups()
'
' groups Macro
'
 
'
    Sheets("Sheet 1").Activate
    Range("J14").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Rows.Group
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Rows.Group

Can I add to this code the conditions that I need?

 

[Migaspt]

Anyone willing to help?

Thanks in advance!

 

[oldbrewer]

see my post11

 

[Migaspt]

see my post11

How do you want me to provide the desired finished table? Can I upload an Excel table into this forum?

I just want to group some rows

 

[oldbrewer]

make a little spreadsheet with raw data and analysed data, select all the cells, copy, go to forum, reply, paste, reply

 

[Migaspt]

What I need is this:

[TABLE="class: cms_table, width: 384"]
<tbody>[TR]
[TD][TABLE="class: cms_table, width: 384"]
<tbody>[TR]
[TD="align: right"][TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]groups[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]TITLE 1[/TD]
[TD]TITLE 2[/TD]
[TD]TITLE 3[/TD]
[TD]TITLE 4[/TD]
[TD]%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]X[/TD]
[TD]X1[/TD]
[TD]X11[/TD]
[TD][/TD]
[TD]98[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]X[/TD]
[TD]X2[/TD]
[TD]X21[/TD]
[TD][/TD]
[TD]87[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]X[/TD]
[TD]X3[/TD]
[TD]X31[/TD]
[TD][/TD]
[TD]20[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]X[/TD]
[TD]X1[/TD]
[TD]X12[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]X[/TD]
[TD]X3[/TD]
[TD]X32[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]X TOTAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]53.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Y[/TD]
[TD]Y1[/TD]
[TD]Y11[/TD]
[TD][/TD]
[TD]68[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Y[/TD]
[TD]Y1[/TD]
[TD]Y12[/TD]
[TD][/TD]
[TD]98[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Y[/TD]
[TD]Y2[/TD]
[TD]Y21[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Y TOTAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]58.44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]56.35[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

O = row to be grouped

Can I use a vb code to group these rows?

PS- Thank you very much for your patience oldbrewer

 

[oldbrewer]

do you want one group of 8 rows or a group of 5 , space, group of 3 ?

 

[Migaspt]

I want to group the Xs and Ys on different groups.

The groups don't have a particular size, it depends on the data.

The code should stop to group rows when it is written "Grand Total" in column A.

 

[Migaspt]

By recording a macro doing what I need I got this code:Code:

Sub groups()
'
'groups Macro

'
 '
    Sheets("Sheet 1").Activate
    Range("J14").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Rows.Group    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Rows.Group

I used the Ctrl+Shift+Down to only select the rows with data to group.

Is it understandable now? I'm not sure how to say it differently