[EXCEL] Looping through rows representing a nested structure

[u/barcode00]

In have a table of data in Excel which represents a nested hierarchical structure. The rows are elements in the structure. All elements are five elements deep. The first five columns of the table represent the level/position of the element. For example, column “Level 1” might have a value of "1", "Level 2” a value of “1.1”, and so on, with the fifth column representing the final element (1.1.1.1.1, 1.1.1.1.2, etc). The other columns describe the names, descriptions of the elements.

I am trying to use VBA to loop through these nested elements with the ultimate goal of creating some documentation of this structure within a Word document with additional notes, etc, in a consistent style.

I have created a PivotTable, which may or not be helpful to my outcome, but it does at least let me see the structure of the parent/child elements. Copying this data into Word from the PivotTable does not make it easy to edit or read which is why I am trying to reconstruct it.

My VBA code is below but of course, it outputs the rows from the columns, rather than the parent item they are from. Maybe there is a better approach altogether! Thank you for any guidance

For Each ptItem In pt.PivotFields("Level 1").PivotItems
  Debug.Print ptItem
    For Each ptItem2 In pt.PivotFields("Level 2").PivotItems
      Debug.Print ptItem2.Name
        For Each ptItem2 In pt.PivotFields("Level 3").PivotItems
          Debug.Print ptItem2.Name
        Next
    Next
Next

[u/fanpages]

... the ultimate goal of creating some documentation of this structure within a Word document with additional notes, etc, in a consistent style...

Ordering/presenting the data using an r/Excel formula/function approach might be possible (without any VBA at all).

A Pivot Table may also make the data (for the result before transposing to r/Word / r/MicrosoftWord) more difficult to collate/prepare, rather than simply sorting the table by the composite key of the first five columns, so as u/Ord3r2Ch4os mentioned, seeing the "consistent style" of the "ultimate goal" would be useful.

[u/ZetaPower]

Don't understand why you are making it this difficult.

Why does every column contain the complete address and not just the position on that level? Seems like a lot of unneeded typing.

Level 1	Level 2	Level 3	Total
1	1	2	1.1.2

If only the last level has a name, what's the point of the nesting?

A description in Word is not that hard, just lay out an example. Why would you describe your entire table in Word?

[u/fanpages → u/ZetaPower]

If only the last level has a name,...

I am guessing due to the nature of the text in the image provided. The sample data could have be presented more clearly.

As each level has a "Name" and "Example" field as descriptive text (and the sample text for each column is the same), the Pivot Table output is probably not indicative of what the output should be (if "Name" and/or "Example" were different for any of the source data rows).

If, however, all the "Name" and "Example" fields for each Level 1 grouping are the same, then, yes, I agree, the presentation of the source data could be reworked accordingly.

[u/barcode00 → u/ZetaPower]

Sorry, my sample data could have been clearer.

All of the levels have proper names, not just the level 5 items. The level 5 items are also all different. My image doesn’t make that clear. The positions are relevant to the desired output so I decided make them distinct columns, despite the massive repetition.

The source data isn’t my own so I am working with what I have

[u/chiibosoil]

So Level 5 has the unique value... What is the starting array structure?

If you are trying to flatten out nested structure, you could use number of methods. Easiest would probably to use Power Query and fill down operation. But if wishing to do it in VBA...

Iterate over Level 5, add it as key to dictionary and add rest as array or concatenated string to item.

But I'm not sure what you are trying to do here.

[u/HFTBProgrammer]

It'd be helpful if A) we had some better idea of your inputs and B) some idea at all of your desired outputs.

Is it that you want to loop on the rows, and within that on the columns? Maybe something like:

Dim r As Long, c As Long, NumRows As Long
NumRows = Cells(Rows.Count, 1).End(xlUp).Row
For r = 1 To NumRows
    For c = 1 To 5
        [something?] = Cells(r, c).Value2
    Next c
Next r

[u/barcode00 → u/HFTBProgrammer]

My sample data wasn’t very clear. Sorry about that.

There are other columns which assign proper names to the levels and naturally the resulting final level 5 items are all different (not obvious from my image). It wouldn’t necessarily only be the position that I am outputting, but also the name of the level (another column).

The output would have each element on a new line (as below) to allow styling to be applied programmatically. This would be in a Word document to represent the structure. For example, a new page for each level 1 item, heading styles for each level type, etc.

Each of the different levels do have corresponding title/name columns, in the same way the final level 5 name and description do, so it wouldn’t necessarily only be the position I am outputting.

1 1.1 1.1.1 1.1.1.1 1.1.1.1.1 1.1.1.1.2 1.1.1.1.3 1.1.1.1.4 1.1.1.1.5 1.1.1.1.6 1.1.1.2 1.1.1.2.1 1.1.1.2.2 1.2 1.2.1.1 1.2.1.1.1 1.2.1.1.2

I’m trying to loop through distinct level 1 items, and within that, loop through distinct level 2 and so on…

Maybe if I simply sort my table by the final level 5 column, the data is already sorted as I have listed above, and I can simply loop through the data as presented, apply my styling logic depending on the length of the level

[u/fanpages → u/barcode00]

...Maybe if I simply sort my table by the final level 5 column, the data is already sorted as I have listed above, and I can simply loop through the data as presented, apply my styling logic depending on the length of the level

As I mentioned above (although you have not replied to me directly), I suspect what you are trying to achieve could be done with MS-Excel formula/function syntax (and you could avoid using VBA).

[u/Ord3r2Ch4os]

What exactly do you want the output to be?

I mean the pivot table doesn't seem to be simplifying your actual data at all, just displaying it differently. Like you could fill in all of the parent data in a table programmatically, but then you just have the original table.

[u/barcode00 → u/Ord3r2Ch4os]

The output would have each element on a new line (as below)

Styling would then be applied programmatically to build back the representation of the structure, appropriate to the level it came from into a Word document. For example, a new page for each level 1 item, heading styles for each level type, etc.

Each of the different levels do have corresponding title/name columns, in the same way the final name and description, so it wouldn’t necessarily only be the position I am outputting.

1 1.1 1.1.1 1.1.1.1 1.1.1.1.1 1.1.1.1.2 1.1.1.1.3 1.1.1.1.4 1.1.1.1.5 1.1.1.1.6 1.1.1.2 1.1.1.2.1 1.1.1.2.2