Convert a single column to a table based on first characters

Question

I've recently started helping with reporting using Essbase/SmartView in Excel. I'm trying to take the Essbase report; which combines all levels of my Project hierarchy into a single column - and instead convert it into a table. It doesn't appear that Essbase has this functionality so I've been trying to figure out how to do it in VBA. Below is an example of what I'm trying to do as well as my macro which only works for populating the Project ID and budget in the table, currently. I'm pretty new to VBA so I'm hoping someone can point me in the right direction on how to approach Levels 1, 2, and 3. I'm thinking my best bet would be some sort of Find based on first two characters eg "L2" but I appreciate any guidance you can provide.

Current

+-----------------+--------+
|     Project     | Budget |
+-----------------+--------+
| P1200           |    150 |
| P1400           |    200 |
| L3 Program 3    |    350 |
| P1100           |    250 |
| P1300           |    150 |
| L3 Program 2    |    400 |
| L2 Initiative 2 |    750 |
| P2200           |    300 |
| P2400           |    200 |
| P2600           |    300 |
| L3 Program 1    |    800 |
| L2 Initiative 1 |    800 |
| L1 Division     |   1550 |
+-----------------+--------+

Desired

+-------------+-----------------+--------------+---------+--------+
|     L1      |       L2        |      L3      | Project | Budget |
+-------------+-----------------+--------------+---------+--------+
| L1 Division | L2 Initiative 2 | L3 Program 3 | P1200   |    150 |
| L1 Division | L2 Initiative 2 | L3 Program 3 | P1400   |    200 |
| L1 Division | L2 Initiative 2 | L3 Program 2 | P1100   |    250 |
| L1 Division | L2 Initiative 2 | L3 Program 2 | P1300   |    150 |
| L1 Division | L2 Initiative 1 | L3 Program 1 | P2200   |    300 |
| L1 Division | L2 Initiative 1 | L3 Program 1 | P2400   |    200 |
| L1 Division | L2 Initiative 1 | L3 Program 1 | P2600   |    300 |
+-------------+-----------------+--------------+---------+--------+

Current Macro

Sub TabularView()

Dim esData As Worksheet
Dim tabView As Worksheet

Set esData = ThisWorkbook.Sheets("Sheet1")
Set tabview = ThisWorkbook.Sheets("TabularView")
rptLR = esData.Cells(Rows.Count, 1).End(xlUp).Row + 1

y = 2

For x = 9 to rptLR 'Data starts in row 9

    If Left(esData.Cells(x, 1).Text, 1) = "P" Then
        tabView.Cells(y,4) = esData.Cells(x,1)
        tabView.Cells(y,5) = esData.Cells(x,2)
        y = y + 1
    End If
Next x
End Sub

So all prefixed with `L#` are actually sub totals? – L42 Mar 27 '18 at 05:13 — L42, Mar 27 '18 at 05:13

score 0 · Accepted Answer · answered Mar 27 '18 at 08:06

See code below, explanation in the comments:

Sub TabularView()

Dim esData As Worksheet
Dim tabView As Worksheet

Set esData = ThisWorkbook.Sheets("Sheet1")
Set tabview = ThisWorkbook.Sheets("TabularView")
rptLR = esData.Cells(Rows.Count, 1).End(xlUp).Row + 1

y = 2

dim L1, L2, L3 as string

'My first suggestion is to work from the bottom up.
'This is because your data appear to be structured
'such that the details for level 1 2 and 3 only 
'appear after the project code they apply to.
'(If you have any say on the structure, I would
'suggest rather reversing the order of the hierarchy
'on the sheet itself instep of looping backwards like this).
For x = rptLR to 9 step -1 'Data starts in row 9

    ' NOTE: Replace this series of If statements with a switch or case statement (whatever VBA has)

    'The next thing to do is if you find an L, then store the name in memory so that we have it in future iterations
    If Left(esData.Cells(x, 1).Text, 2) = "L1" Then
        L1 = esData.Cells(x,1).value
    End If
    If Left(esData.Cells(x, 1).Text, 2) = "L2" Then
        L2 = esData.Cells(x,1).value
    End If
    If Left(esData.Cells(x, 1).Text, 2) = "L3" Then
        L3 = esData.Cells(x,1).value
    End If

    If Left(esData.Cells(x, 1).Text, 1) = "P" Then
        'Finally, just use the L values we kept in memory in previous iterations
        tabView.Cells(y,1) = L1
        tabView.Cells(y,2) = L2
        tabView.Cells(y,3) = L3
        tabView.Cells(y,4) = esData.Cells(x,1)
        tabView.Cells(y,5) = esData.Cells(x,2)
        y = y + 1
    End If

Next x
End Sub

Convert a single column to a table based on first characters

1 Answers1