Creation of a hierarchically structured report based on a control table

Gerrit Schang 0
Viewed 24

today, I have been trying to build a hierarchy based on a table like this:

Item Cat 1 Cat 2 Cat 3 Quantity
001 Apple Fruit Store 1 100
002 Orange Fruit Store 1 100
003 Cucumber Vegetable Store 1 100
004 Tomatoe Vegetable Store 1 100
005 Apple Fruit Store 2 100

The hierarchy in Excel should look like this:

Category Quantity
.... Apple 100
.... Orange 100
..[-] Fruit 200
.... Cucumber 100
.... Tomatoe 100
..[-] Vegetable 200
[-] Store 1 400
.... Apple 100
..[-] Fruit 100
[-] Store 2 100

For each category there should be a totals row (below) that also groups the corresponding item rows above (with expand/ collapse).

I started with a simple loop over each row of the control table, that detects when a CAT3/2/1 value has changed and then writes a totals row. However, this turned out to be quite messy after a while.

My second attempt was to create three arrays with distinct categories and looping through these arrays. That worked but took a a long time and seemed inefficient.

I was wondering if anyone has any other ideas?

Sorry for not showing any code. I would like to keep it rather abstract for now.

Thanks in advance!

Gerrit

vba excel
1 Answers

for what it's worth, this is how I solved the problem (not pretty - but works):

For Each ControlTableRow In ControlTable.Rows

    If ControlTableRow.Row = 1 Then GoTo skip_this

    Cat1 = ControlTableRow.Cells(2).Value
    Cat2 = ControlTableRow.Cells(3).Value
    Cat3 = ControlTableRow.Cells(4).Value
    
    'remember previous categories (i.e. hierarchy levels)
    If ControlTableRow.Row = 2 Then
        Cat1_prev = Cat1
        Cat2_prev = Cat2
        Cat3_prev = Cat3
    End If

    If Cat1_prev <> "" And Cat1 <> Cat1_prev Then 'Cat1 needs to be summed up
        SumLevel = 1
        write_row_in_target_table '1. totals row
        Cat1_prev = Cat1 'reset
    End If
    
    If Cat2_prev <> "" And Cat2 <> Cat2_prev Then 'Cat2
        SumLevel = 2
        write_row_in_target_table '2. totals row
        Cat2_prev = Cat2
    End If
    
    If Cat3_prev <> "" And Cat3 <> Cat3_prev Then 'Cat3
        SumLevel = 3
        write_row_in_target_table '3. totals row
        Cat3_prev = Cat3
    End If
    
    SumLevel = 0 'I am a leaf row (lowest level)
    write_row_in_target_table

Next ControlTableRow

   'Grand Total

    Cat1 = "the end"
    Cat2 = "the end"
    Cat3 = "the end"
   
    If Cat1_prev <> "" And Cat1 <> Cat1_prev Then 'Cat1 
        SumLevel = 1
        write_row_in_target_table
        Cat1_prev = Cat1
    End If
  
    If Cat2_prev <> "" And Cat2 <> Cat2_prev Then 'Cat2
        SumLevel = 2
        write_row_in_target_table
        Cat2_prev = Cat2
    End If
    
    If Cat3_prev <> "" And Cat3 <> Cat3_prev Then 'Cat3
        SumLevel = 3
        write_row_in_target_table
        Cat3_prev = Cat3
    End If

Based on the value of "SumLevel" the procedure "write_row_in_target_table" writes a totals row that sums and groups the sub nodes/ leaves.

Kind regards! Gerrit

Gerrit Schang0
Related