Outlining with condition? part 2

[Sige]

Dear NG,

I started a thread about Outlines before ... but got a bit stuck on
MULTI-level outlining.

Summary:
-The grouping should be based on a condition in column B:
ie: Is the font of this cell BOLD?
-Evaluation of this condition always starts on row 7.

Dave Peterson (thanks Dave!)tried to understand what I want to do and
we ended up with the following visualisation of my problem:

My visualisation:
B7:NOT
B8:NOT
1 B9:BOLD =>outline level1 (grouping row7-8)
B10:NOT
B11:NOT
B12:NOT
2 B13:BOLD =>outline level1 (grouping row10-12)
B14:NOT
3 B15:BOLD =>outline level1 (grouping row14)
B16:NOT
B17:NOT
4 B18:BOLD =>outline level1 (grouping row 16-17)
1 B19:BOLD => outline level2 (grouping row 7-18)
B20:NOT
5 B21:BOLD =>outline level1 (grouping row20)
B22:NOT
B23:NOT
6 B24:BOLD => outline level1 (grouping row22-23)
5 B25:BOLD => outline level2 (grouping row20-24)
1 B26:BOLD => Outline level3 (grouping row 7-25)

Dave says:
The numbers were my feeble attempt to "group" the rows.
1 is a new group, 2 is a new group, 3 a new group, 4 a new group.
But B19 is grouped with top of group 1
5 & 6 (b21 & b24) are new groups
But 5 (b25) is grouped with 5
Why would B26 jump all the way back to 1 (and not 4)?
(rest of discussion on thread:Outlining with condition?)

Sige says:
Level-2 outlines group all the level-1 outlines which are ABOVE...until
it meets another level-2 outline

Level-3 outlines group all the level-2 outlines which are ABOVE
....untill it meets another level 3 outline

Level-4 group all Level-3 outlines which are ABOVE
....untill it meets another level 4 outline
....

ANY Wizard who is able to help me out to set the Multi-Level outlining,
please?

Please please please, Sige



PS: I can mail you an excel-sheet with some manually set outlines on if
it could help you to better see my problem.

Dave's first attempt was already a step in the right direction
Option Explicit
Sub testme2()
Dim wks As Worksheet
Dim NextBoldCell As Range
Dim CurBoldCell As Range
Dim RowDiff As Long
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set wks = Worksheets("Sheet1")
wks.Rows.ClearOutline 'clear previous testing

With wks
FirstRow = 6
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
'end with a bold cell
With .Cells(LastRow, "B")
.Value = "DUMMY"
.Font.Bold = True
End With

Set CurBoldCell = .Cells(FirstRow, "B")

For iRow = FirstRow + 1 To LastRow
If IsBold(.Cells(iRow, "B")) Then
Set NextBoldCell = .Cells(iRow, "B")
RowDiff = NextBoldCell.Row - CurBoldCell.Row
If RowDiff > 1 Then
'.Rows(NextBoldCell.Row + 1).Resize(RowDiff -
1).Select
.Rows(CurBoldCell.Row + 1).Resize(RowDiff -
1).Group
End If
If IsBold(NextBoldCell.Offset(1, 0)) Then
'don't change starting point
Else
Set CurBoldCell = NextBoldCell
End If
End If

Next iRow
With .Cells(.Rows.Count, "B").End(xlUp)
.ClearContents
.Font.Bold = False
End With
End With
End Sub

Function IsBold(myCell As Range) As Boolean
IsBold = False
If myCell(1).Font.Bold = True Then
IsBold = True
End If
End Function

 

[Dave Peterson]

I still don't have a real answer, but I think you have to define why those
numbers are assigned to each of those rows--what makes them different.

Why is that first 3 a 3 and not a 1?

I assigned those numbers based on your notes--but that didn't give me any
insight to why you grouped them the way you did.

Maybe someone else sees a pattern, but I don't.

 

[Sige]

Hi Dave,
I'll explain it differently:

Imagine: (this BASEDATA to be pasted in A1)
Category Product Group Product Quality Price
Food Apple 1 1
Food Apple 1 1
Food Apple 2 1
Food Apple 2 1
Food Orange 1 2
Food Orange 2 2
Food Orange 2 3
Food Orange 2 3
Fuel Petrol 1 5
Fuel Petrol 1 6
Fuel Petrol 1 7
Fuel Petrol 1 8

Now: (with Summary below data checked, Replace current subtotals
un-checked)
Step 1: make SUBTOTALS: at each change in "Category" use function
"sum" add subtotal to "Price"

Step 2: make SUBTOTALS: at each change in "Product Group" use function
"sum" add subtotal to "Price"

Step 3: make SUBTOTALS: at each change in "Product Quality" use
function "sum" add subtotal to "Price"

You get exactly the lay-out of the data I would like to achieve! (with
exception of 2 Grand Totals too many)

If you imagine that Column "Price" inherits the Boldness of all
Subtotals (from Column A,B&C)
(Bold cells in column Price will be then in this example:
row:4,7,8,10,14,15,16,21,22,23,24 (25,26)
====> This is my SIGE_STARTDATA!

On this SIGE_STARTDATA (being in Column B) I wish it could be possible
to set the same groupings .... as if started with the BASEDATA and
applied the Subtotal-procedure 3 times on it.

Hope this made it clearer ... Sige

 

[Dave Peterson]

Oh, my.

I think I'd trash all that other stuff and just record a macro when I did the
all the data|subtotals.

Then come back and wipe out those extra grand totals lines. They're at the
bottom, just go up from the last cell in column A and delete/clear contents.

with activesheet
.cells(.rows.count,"A").end(xlup).offset(0,-1).resize(2).entirerow.delete
end with

By using the built in functions, you'll even get code that makes sense and can
be modified <vbg>.

 

[Sige]

Hm...

Hi Dave,

Thanks for still trying to follow my "confusion"! ))
Actually, I do not want to set Subtotals starting from a BASEDATA-set.
On the contrary ...

My starting point is a dataset which IS ALREADY STRUCTURED as IF the
"Subtotals-procedure" has been applied (has no formulas, is just a dump
with values Bold /Not Bold)
=======> but the outlines/groupings are missing !!!!
=================> it are those which i would like to set!
(though NOT via Subtotals as my dataset is already laid-out/summed like
that & i do not want to do it twice
; on top of it I would not be able to do it as my Columns: Category,
Product Group, Quality are missing )

My Starting point is therefor a Column B (like "Price")

-formatted (BOLD vs NOT BOLD):
What you think????


Do you understand why I never had ambition to become a teacher?! )))
Sige

 

[Dave Peterson]

First, check to see if the outlining symbols have been turned off:
tools|options|view tab

If those columns are missing, I don't think it's possible (well, for me anyway)
to recreate what did the outlining.

Yell at the person who did this to either hide those columns or not delete
them. Then lower your sights <bg> and use data|subtotals!

 

[Sige]

))

Come on Dave after 500 lines of explanation?! jk

The reports I get are exported by another system ... unfortunately
there are no outlines (they have not been turned off!) to get a clearer
sight on this data with some outlines. Specially as some 50 sheets a
day with +/-1000 rows have to be evaluated.

Spread the word if smeone is looking for a challenge ;o)
Living in hope ...
Sige

 

[Dave Peterson]

Sorry, I got nothing!

))

Come on Dave after 500 lines of explanation?! jk

The reports I get are exported by another system ... unfortunately
there are no outlines (they have not been turned off!) to get a clearer
sight on this data with some outlines. Specially as some 50 sheets a
day with +/-1000 rows have to be evaluated.

Spread the word if smeone is looking for a challenge ;o)
Living in hope ...
Sige