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
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