generating a tree like structure using parent-child ID's with VBA

Question

I have the following four columns, where I need to iterate over 'item':

parentID    parent  itemID  item
398735  Papa    398713  Alpha
398735  Papa    399270  Bravo
398735  Papa    399822  Charlie
398731  Lima    398732  Delta
398731  Lima    398733  Echo
398733  Echo    66359   Foxtrot
398733  Echo    66360   Golf
398733  Echo    66361   Hotel
398733  Echo    66362   India
398733  Echo    66363   Juliett
398733  Echo    66364   Kilo
398730  Root    66281   Mike
398730  Root    66283   November
398730  Root    398731  Lima
398730  Root    398734  Oscar
398734  Oscar   398735  Papa
398734  Oscar   66281   Quebec

I want to generate a dynamic hierarchy (tree-like structure), like so:

Mike
November
Lima
    Delta
    Echo
        Foxtrot
        Golf
        Hotel
        India
        Juliett
        Kilo
Oscar
    Papa
        Alpha
        Bravo
        Charlie
    Quebec

If this was in Python for example, it would be extremely easy to create a hierarchy by iterating over my items, looking to see if its a parent (root if not), and using a dictionary to store all this information, then build this structure on an excel sheet by iterating over the dictionary. However, for the purposes of this Macro I have no idea how to even do a simple column lookup, let alone use a dictionary.

My theory is to:

1) Begin by somehow iterating over the four "roots", and store them as keys with null values in dict1.

2) For each root, update it's value in dict1 to be an array of immediate children.

3) Then, for each child of each root, create another dictionary (dict2) where all keys are all the root-children.

4) Iterate over dict2 keys and set each key value to an array of children.

5) Iterate over dict1, write everything, reference dict2 as you go for further children.

Can this be implemented in VBA elegantly?

https://stackoverflow.com/questions/9821545/how-to-build-parent-child-data-table-in-excel should be of some interest — Pankaj Jaju, Aug 15 '18 at 14:11
@PankajJaju, it is an interesting solution, however it is dependent on sorting. Order is not a criteria of my problem, the issue inherently is associating one or multiple children with 1 parent - ideally using the VBA dictionary, which I'm not sure how to implement. — Évariste Galois, Aug 15 '18 at 14:19
Sorting should be easy in Excel. You can either do it manually using Excel' Sort feature or you can record a macro of it. For example, you can sort it based on ParentID and then by ItemID/Name. — Pankaj Jaju, Aug 15 '18 at 14:22
I think you misunderstood my comment, I don't believe sorting could be useful here. I updated my post with the theory, I was thinking that perhaps recursion could be useful but I'm not sure if that is beneficial. — Évariste Galois, Aug 15 '18 at 14:37
How are you going to identify the root in this case? Based on itemID or if the text is Root? — Pankaj Jaju, Aug 15 '18 at 14:39
I believe for consistency, it would be better to identify everything by ID's, so by itemID. — Évariste Galois, Aug 15 '18 at 14:42

score 3 · Accepted Answer · answered Aug 15 '18 at 16:03

3

It's a little hard for me to understand exactly what you want, but I think it should be done like this.

Sub newlist()
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
w2.Cells(1, 1).Value = w1.Cells(1, 1).Value
w2.Cells(1, 2).Value = w1.Cells(1, 2).Value
Ide = Cells(1, 1).Value
w1.Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
k = 3
kk = 1
For i = 2 To n
If w1.Cells(i, 1).Value = Ide Then
w2.Cells(kk, k).Value = w1.Cells(i, 2).Value
k = k + 1
Else
kk = kk + 1
k = 3
Ide = w1.Cells(i, 1).Value
w2.Cells(kk, 1).Value = Ide
w2.Cells(kk, 2).Value = w1.Cells(i, 2).Value
End If
Next
End Sub

Before:

After:

answered Aug 15 '18 at 16:03

ASH

20,759
19
87
200

This is really useful... would you mind including a couple comments so I can understand what's going on? I'm a little lost trying to understand this. – Évariste Galois Aug 15 '18 at 16:31
You can create a helper sheet, copy/paste columns 9 & 11 into columns 1 & 2, and it will work fine. Or, feel free to adjust the script that I posted. Basically, Cells(1, 1), translates to Cells(Row, Column). So, Cells(1, 1), is equal to Cell A1. – ASH Aug 15 '18 at 20:34
I see. Rather than growing the children horizontally, if I wanted to match the cell-formatting in the table in the post, how would I do so? The end goal is that format, and it's not efficient to create a sheet as an intermediary process to achieve that to the best of my knowledge, if there is a simple way to do so. – Évariste Galois Aug 15 '18 at 22:43

score 1 · Answer 2 · answered Aug 16 '18 at 00:27

Ok, I made a small tweak to the original code. Try this and see how you get along.

Sub newlist()
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
w2.Cells(1, 1).Value = w1.Cells(1, 1).Value
w2.Cells(1, 2).Value = w1.Cells(1, 2).Value
Ide = Cells(1, 1).Value
w1.Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
k = 3
kk = 1
For i = 2 To n
If w1.Cells(i, 1).Value = Ide Then
w2.Cells(kk + 1, 2).Value = w1.Cells(i, 2).Value
kk = kk + 1
k = k + 1
Else
kk = kk + 1
k = 3
Ide = w1.Cells(i, 1).Value
w2.Cells(kk, 1).Value = Ide
w2.Cells(kk, 2).Value = w1.Cells(i, 2).Value
End If
Next
End Sub

Before:

After:

generating a tree like structure using parent-child ID's with VBA

2 Answers2

Linked