Преобразование одного столбца в таблицу на основе первых символов

Question

Недавно я начал помогать с отчетами, используя Essbase/SmartView в Excel. Я пытаюсь взять отчет Essbase; который объединяет все уровни моей иерархии проекта в один столбец - и вместо этого преобразует его в таблицу. Похоже, что Essbase не обладает такой функциональностью, поэтому я пытался выяснить, как это сделать в VBA. Ниже приведен пример того, что я пытаюсь сделать, а также мой макрос, который в настоящее время работает только для заполнения идентификатора проекта и бюджета в таблице. Я довольно новичок в VBA, так что я надеюсь, что кто-то может указать мне правильное направление на то, как приблизиться к уровням 1, 2 и 3. Я думаю, что моя лучшая ставка - это что-то вроде Find, основанное на первых двух персонажах. например, "L2", но я ценю любые рекомендации, которые вы можете предоставить.

Текущий

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

Желаемый

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

Текущий макрос

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

Answer 1

Смотрите код ниже, объяснение в комментариях:

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