0

Am running Excel 2013 and Windows 10. Have a structure similar to figure 1 below:

tree

Thanks to gserg in this answer, I can track the depth change from one row to the next, as shown in figure 2 below:

tree with depth indicator

How can I use that information, or a comparable calculation, to generate a numeric outline as shown in column J of figure 3 below:

tree with depth indicator and manual numeric outline

A solution needs to work when depth is 27 levels and rows are 1,000+

Community
  • 1
  • 1
Jay Gray
  • 1,706
  • 2
  • 20
  • 36

1 Answers1

2

This is based on your example of 7 columns, but it's easily expanded by changing the array size and columns variable to 27 (or however many you have). Let me know if it's oversimiplified -- I realize your sample data may be just that and your actual data has more complexity than this.

Sub Outline(ws As Worksheet, columns As Integer, offset As Integer)
  Dim row, lastRow As Long
  Dim index, col As Integer
  Dim level As String

  Dim values(1 To 255) As Integer
  values(1) = 0

  lastRow = ws.UsedRange.Rows.Count

  For row = 1 To lastRow
    For index = 1 To columns
      If ws.Cells(row, index).Value <> "" Then
        values(index) = values(index) + 1
        level = values(1)

        If index > 1 Then
          For col = 2 To index
            level = level & "." & values(col)
          Next col
        End If

        ws.Cells(row, columns + offset).NumberFormat = "@"
        ws.Cells(row, columns + offset).Value = level

        For col = index + 1 To columns
          values(col) = 0
        Next col

        Exit For
      End If
    Next index
  Next row
End Sub

enter image description here

Hambone
  • 15,600
  • 8
  • 46
  • 69
  • That is a beautiful thing! I tested on 13x11000 array and it worked perfectly. I have one tiny issue. I try to keep all macros in a single workbook, then load that workbook when I need to run a macro from that macro lib. I cannot run your macro using that strategy because of the 'tab-specific' identifier. Is it easy to update your solution with a 'workbook-tab' identifier so that I can externalize the macro? If not, the current solution 'does the work' and I'll use it as is. – Jay Gray Jan 03 '17 at 11:41
  • Also, you may wish to update your answer for non-proficient macro users with the open/close statement e.g. `Sub Outline() 'by Hambone 'http://stackoverflow.com/questions/41432134/how-to-generate-a-numeric-outline-in-excel Dim ws As Worksheet ... Sub ` As documentation for other users, the solution is generated in the first column after the defined array. – Jay Gray Jan 03 '17 at 11:42
  • Thanks for the feedback... I'm happy it worked. Was it quick? I wrapped the code in a function wrapper and made the worksheet, number of columns and the offset (where the output is placed, relative to the last column) all parameters. I hope this is what you had in mind. Now, in the example above, you could call it as `Outline Sheets("Sheet1"), 7, 1` per the example or scale it to be a different number of columns, etc. Again, thanks for the feedback. – Hambone Jan 03 '17 at 12:15
  • It is quick. IMHO @Hambone this is a very valuable script. TTBOMK, there is only one other reliable outline generator - MS Word. One can copy a tab-delimited outline (i.e. no terminating 'x') to Word and 'outline' using the outline selectors. However, the limit is 9 levels. Many, many hierarchies > 9 levels deep. Before your script, there was not systematic means to create a 9+ level outline. – Jay Gray Jan 03 '17 at 12:27