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:

TITLE 1	TITLE 2	TITLE 3	TITLE 4	%
X	X1	X11		98
X	X2	X21		87
X	X3	X31		20
X	X1	X12		10
X	X3	X32
X TOTAL				53.75
Y	Y1	Y11		68
Y	Y1	Y12		98
Y	Y2	Y21		14
Y TOTAL				58.44
…
Grand Total			56.35

<tbody>
</tbody>

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]

It is not at all clear what you want. Put in a second table with your desired outcome.

 

[Migaspt]

This is what I need:

	A	B	C	D	E
1	TITLE 1	TITLE 2	TITLE 3	TITLE 4	%
2	X	X1	X11		98
3	X	X2	X21		87
4	X	X3	X31		20
5	X	X1	X12		10
6	X	X3	X32
7	X TOTAL				53.75
8	Y	Y1	Y11		68
9	Y	Y1	Y12		98
10	Y	Y2	Y21		14
11	Y TOTAL				58.44
12	…
13	Grand Total			56.35

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

Rows to group: 2-6; 8-10; 12

 

[oldbrewer]

but that is what you have to start with or do you mean eliminate total and g total rows from display

 

[Migaspt]

but that is what you have to start with or do you mean eliminate total and g total rows from display

This is what I start with

 

[oldbrewer]

do you want row2 to row6 then 8 to 10 then 12 in a new table with no headers

1....2
4....5
*****
5....7
12...13
15....16
******
27....29

you want

1....2
4.....5
12....13
15....16

 

[Migaspt]

What I want is to group rows but leave the total out of the group.

Is it possible to write code for conditional grouping?
Like, group rows if next row's A cell does not have total on the text?

I do not want to delete the rows with total. I want to group the rows that do not have "total" written on their A cell

"do you want row2 to row6 then 8 to 10 then 12 in a new table with no headers": No I only need to group the rows on this table. I do not need to create a new table.

I need to do this manually, group rows until the "X Total" row (group the data). The "total" rows are in a different colour aswell. I'll try to upload the actual table for better understanding

 

[Migaspt]

Here is the table I start with:

HR GL Dept Org Tree Lvl 6	HR GL Dept Org Tree Lvl 7	HR Dept Manager Name L1	HR Dept Manager Name L2	HR Dept Manager Name L3	Current Supervisor	Avg Utilization % - Week
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	X	0.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	X	0.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	X	0.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	X	0.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	X	0.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	X	0.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	X	0.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	X
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	X
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	X	0.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	X	0.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Y	50.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Y	0.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Y	0.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Y	0.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Y
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Y
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Y	75.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Y
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Y
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Y	0.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Y	0.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Y
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Y
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Y
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Y	85.0%
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Z
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Z
BELGIUM_ADMIN	BELG_ADM_OTHER	(blank)	0	(blank)	Z	0.0%
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	Z
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	Z
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	Z
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	Z
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	Z
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	Z	0.0%
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	Z
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	Z
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	Z
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	Z
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	Z
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	Z
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	T	98.0%
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	T	0.0%
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	T	0.0%
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	T
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	T
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	T	0.0%
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	T	0.0%
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	T	100.0%
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	T	0.0%
BELGIUM_ADMIN	BELG_GLOB_FUNCT	(blank)	0	(blank)	T	0.0%
BELGIUM_ADMIN Total						15.1%
BELGIUM_SIC	PRACTICES_T2	(blank)	0	(blank)	T
BELGIUM_SIC	PRACTICES_T2	(blank)	0	(blank)	T	0.0%
BELGIUM_SIC Total						0.0%
Grand Total						7.6%

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

I am sorry but it is no clearer to me. What do you mean by group. Do you want a summary table with how many of each group there are. Read my post 6 again and answer my question please. I am not being critical. It is hard for somebody very familiar with a topic to describe an issue to somebody who knows nothing about it.

 

[Migaspt]

I am sorry but it is no clearer to me. What do you mean by group. Do you want a summary table with how many of each group there are. Read my post 6 again and answer my question please. I am not being critical. It is hard for somebody very familiar with a topic to describe an issue to somebody who knows nothing about it.

I'm sorry, maybe I'm not explaining myself so good.

What I mean by group rows is the function on Excel when you clik the "Data" tab and then "Group" (It has the "Ungroup" and "Subtotal" buttons on the right)