i want to apportion a single value to multiple rows based on certain logic in excel

Question

I have a grouped data at multiple levels (Child-Parent activities). There are multiple parent child levels below.I want to distribute a value (eg. 1,000,000 points) to the below child activities based on certain weights given to each child. Snapshot added for reference. Can somebody please help?

Snapshot of the heirarchial grouped data:

Please show us what you have got so far, and what part you are stuck on/errors out. Check [ask] a question with a [mcve] — JvdV, Mar 05 '19 at 07:31
I am trying to write a code for the same. But don't know how to proceed ahead. The values shown in the screen shot is manually enter. Its quite difficult to enter values for 2000 rows. — Ankit Singh, Mar 05 '19 at 07:39
You could try [Grouping Rows in VBA](https://stackoverflow.com/questions/13337267/grouping-rows-in-vba), is not exactly your example, but should be a good start on how to deal with grouping in VBA. — FAB, Mar 05 '19 at 08:05
Grouping will be done by me manually as its quite difficult to code grouping logic in VBA. I just want to distribute the points according to weight-age provided to each row. — Ankit Singh, Mar 05 '19 at 08:15
As you can see in screen shot row number 22 (WBS 1.3) is 20% of 1,000,000 i.e. 200000. while when we go down on row number 23 ( WBS 1.3.1 is sub part of row number 22) i.e. 8% of 200000. that is don't understand how to crack in vba. — Ankit Singh, Mar 05 '19 at 08:23
@AnkitSingh to group in VBA is extremely simple. You just need to know how many levels in you need to group by, you can do that with your numbering system. As for the weighting, are you saying a child’s value should be the percentage of the calculated parent value? So all children weight/age will add up to 100%? Does my question make sense? — Skin, Mar 05 '19 at 09:04
@BradDixon, the number on level may change as per the project we are doing but for a particular project the number of level will be constant. Yes, You are correct in understanding that child's value will be a percentage (weight) of the parent value. So all children weight will add up to 100% — Ankit Singh, Mar 05 '19 at 09:33
If you open another question, I'll answer the grouping question. I've had to do it previously myself based on the exact data set you have ... if I understand correctly that is. — Skin, Mar 05 '19 at 09:44
@BradDixon As I am a new user I don't know stack overflow will allow me to ask same question again. Will you Please help me in this? — Ankit Singh, Mar 05 '19 at 09:55
@AnkitSingh you literally just ask another question, like you did with this one. — Skin, Mar 05 '19 at 10:06
@BradDixon https://stackoverflow.com/questions/55000805/grouping-according-to-parent-child-relationship-by-excel-vba — Ankit Singh, Mar 05 '19 at 10:38

score 0 · Accepted Answer · answered Mar 05 '19 at 09:53

Add this code to your project and see if it works. Go to the VBA editor and make sure you drop that into a new module ...

Public Function CalculateWeightedPoints(ByVal rngData As Range, ByVal strID As String, ByVal dblWeighting As Double) As Double
    Dim lngRow As Long, strThisID As String, strParent As String, arrParent() As String, i As Long, objCell As Range
    Dim dblParentValue As Double, lngCValueCol As Long, rngCaller As Range, lngWSRow As Long, lngWSCol As Long

    Application.Volatile

    Set rngCaller = Application.Caller
    lngValueCol = rngCaller.Column

    arrParent = Split(strID, ".")

    ' Determine the parent ID.
    For i = 0 To UBound(arrParent) - 1
        If i > 0 Then strParent = strParent & "."
        strParent = strParent & arrParent(i)
    Next

    ' Find the parent value, it will be used to weight the children.
    For lngRow = 1 To rngData.Rows.Count
        Set objCell = rngData.Cells(lngRow, 1)

        strThisID = Trim(rngData.Cells(lngRow, 1))

        lngWSRow = objCell.Row
        lngWSCol = rngCaller.Column

        If strThisID = "" Then Exit For

        If strThisID = strParent Then
            ' Get the value of the parent.
            dblParentValue = objCell.Worksheet.Cells(lngWSRow, lngWSCol)
            Exit For
        End If
    Next

    On Error Resume Next

    Err.Clear

    CalculateWeightedPoints = dblWeighting * dblParentValue

    If Err.Description <> "" Then
        CalculateWeightedPoints = 0
    End If
End Function

... then in your data matrix, add the following formula into the cell that you want to calculate the weighted value for. 1 thing, make sure the formula starts from 1.1, not the first WBS. The first WBS needs to have the originating value.

=CalculateWeightedPoints($A$1:D2,A3,C3)

... fill the formula down from there and hopefully it works for you.

Just make sure that the ID (i.e the WBS) needs is in the first column of the range, you have that though so that should be fine.

This image shows the calculated values, I hope it's what you're after.

No worries, glad I could help. If you want to be able to generate the group and outline automatically, let me know the question and I'll get the answer to you. — Skin, Mar 05 '19 at 10:11
Dixan Please answer my new question. https://stackoverflow.com/questions/55000805/grouping-according-to-parent-child-relationship-by-excel-vba — Ankit Singh, Mar 05 '19 at 10:37

i want to apportion a single value to multiple rows based on certain logic in excel

1 Answers1