CODEWITHSUNDEEP
excelvbaparent-child
Ushay
Ushay

Reputation: 127

classification of grouped items based on parent child hierarchy

I have several thousands of lines with parent-child hierarchies and a sample I am sharing here. The data has hierarchy levels starting from 0 to 10, but for me from level 3 and above are important as I am calculating the weight of parent level 3 which is dependant on it's child and sub child levels.

enter image description here

From column L to P, I have shown the hierarchy, where 3 is the parent, 4 is child and some childs 4 are then classified to 5,6,7... so on. The weight of parent 3 is sum of all 4's, where sum of 4's is again sum of 5's and so on..

I tried to initially write the parent info. of each child by putting here in C7 the following formula =IF(B7>3;IF(B7>B6;D6;C6);"")

which works fine till row 6 and then fails as the level here changes from 6 to 5. See the image below

enter image description here

So I realised that Excel formula will not be sufficient here to extract all the parent info. Also cell F6 is again classified based on material are again dependent on child.

could anyone please tell how to proceed with vba for extracting the parent info. and the weight classification? A few lines of code would be a great help for me to head start.

Thanks a lot in advance!

Upvotes: 0

Views: 610

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Please, test the next code. You did not answer my clarification question and the following code assumes that you did not show us the correct weight for second 5:

Sub CalculateWeight()
   Dim sh As Worksheet, lastR As Long, arr, arrC, ref As Long, i As Long, j As Long, k As Long

   Set sh = ActiveSheet
   lastR = sh.Range("B" & sh.rows.count).End(xlUp).row
   arr = sh.Range("B3:D" & lastR).Value 'put the range to be processed in an array, for faster iterations
   ReDim arrC(1 To UBound(arr), 1 To 1) 'redim the final array for the same number of rows like arr

   For i = 1 To UBound(arr)             'iterate between the array rows
        If arr(i, 1) > 3 Then           'if the hierarchy value is > 3:
            Do While (arr(i + j, 1) > 3)'loop until the next 0
                ref = i - 1             'memorize the row keeping the third hierarchy
                If arr(i + j, 1) = arr(i + j - 1, 1) + 1 Then      'if the value in column 1 is less with a unit thay precedent
                     arrC(i + j, 1) = arr(i + j - 1, 3): j = j + 1 'take the value of the precedent row, third column
                Else
                    For k = i + j To ref Step -1                   'iterate backwards
                        If arr(i + j, 1) = arr(k - 1, 1) + 1 Then  'when find the hierarchy less with a unit
                            arrC(i + j, 1) = arr(k - 1, 3): j = j + 1: Exit For 'take the value of third column and exit iteration
                        End If
                    Next k
                    
                End If
                If i + j > UBound(arr) Then Exit For  'exit iteration if it exceeds the array number of elements
            Loop
        Else
            arrC(i, 1) = ""                            'for lines before each 3
        End If
        If j > 0 Then i = i + j - 1: j = 0  'reinitialize variables
   Next i
   sh.Range("C3").Resize(UBound(arrC), 1).Value = arrC 'drop the array content at once
End Sub

Upvotes: 1

Related Questions