Recursion VBA finding percentage

[vds1]

Hello Everyone,

Happy New Year

Using the below table i would like to determine % of corp and % of Ind mentioned in column E and F.

• if the flag is Corp then Corp% is 100%
• if the flag is Ind then Ind% is 100%

Above two cases are simple and straight forward. But when the flag is fund then the Client Account number should be looked at Fund Account, in this case 16 is searched in column B and we see two rows both contributing equally to Ind% and Corp%. Hence 50% is allocated to both Corp% and Ind%.

So the calculation is
= 150+150=300
= Corp%(14)+Ind%(13)
Therefore,
=50%+50% (By dividing 300 to both sides)

Hence fund account 27 has 50% to Ind% and 20% to Corp%

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]CLAccount[/TD]
[TD]Fund Account[/TD]
[TD]Value[/TD]
[TD]Flag[/TD]
[TD]Corp%[/TD]
[TD]Ind%[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]21[/TD]
[TD]200.00[/TD]
[TD]Corp[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]22[/TD]
[TD]100.00[/TD]
[TD]Corp[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]16[/TD]
[TD]150.00[/TD]
[TD]Ind[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]16[/TD]
[TD]150.00[/TD]
[TD]Corp[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]25[/TD]
[TD]300.00[/TD]
[TD]Corp[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]27[/TD]
[TD]450.00[/TD]
[TD]Fund[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]














There can also be fund of fund, which can be even more complex, like below table, the fund account 28 is a fund of fund. So the client account 27 holds 16 which is again fund and 16 holds 13 and 14.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]CL Account[/TD]
[TD]Fund Account[/TD]
[TD]Value[/TD]
[TD]Flag[/TD]
[TD]Corp%[/TD]
[TD]Ind%[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]21[/TD]
[TD]200.00[/TD]
[TD]Corp[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]22[/TD]
[TD]100.00[/TD]
[TD]Corp[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]16[/TD]
[TD]150.00[/TD]
[TD]Ind[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]16[/TD]
[TD]150.00[/TD]
[TD]Corp[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]25[/TD]
[TD]300.00[/TD]
[TD]Corp[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]27[/TD]
[TD]450.00[/TD]
[TD]Fund[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]28[/TD]
[TD]500[/TD]
[TD]Fund[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[/TR]
</tbody>[/TABLE]













Hope that makes sense. Many thanks for your help

 

[FormR]

Hi, something like this perhaps.

Excel 2013/2016
	A	B	C	D	E	F
1	CLAccount	Fund Account	Value	Flag	Corp%	Ind%
2	11	21	200	Corp	100%	0%
3	12	22	100	Corp	100%	0%
4	13	16	150	Ind	0%	100%
5	14	16	150	Corp	100%	0%
6	15	25	300	Corp	100%	0%
7	16	27	450	Fund	50%	50%
Sheet1

Cell Formulas
Range	Formula
E2	=IF(D2="Corp",1,IF(D2="Fund",COUNTIFS($B$2:$B$7,A2,$D$2:$D$7,"Corp")/COUNTIFS($B$2:$B$7,A2),0))
F2	=IF(D2="Ind",1,IF(D2="Fund",1-E2,0))

 

[wideboydixon]

Book1
	A	B	C	D	E	F
1	CLAccount	Fund Account	Value	Flag	Corp%	Ind%
2	11	21	200	Corp	100%	0%
3	12	22	100	Corp	100%	0%
4	13	16	150	Ind	0%	100%
5	14	16	150	Corp	100%	0%
6	15	25	300	Corp	100%	0%
7	16	27	450	Fund	50%	50%
8	27	28	500	Fund	50%	50%
Sheet1

Public Sub FindPercentage()

Dim lastRow As Long
Dim thisRow As Long
Dim corpValue As Double
Dim indValue As Double
Dim fundAccount As Variant

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For thisRow = 2 To lastRow
    Select Case Cells(thisRow, "D").Value
        Case "Corp"
            Cells(thisRow, "E").Value = 1
            Cells(thisRow, "F").Value = 0
        Case "Ind"
            Cells(thisRow, "E").Value = 0
            Cells(thisRow, "F").Value = 1
        Case "Fund"
            fundAccount = Cells(thisRow, "A").Value
            indValue = 0
            corpValue = 0
            
            Do Until Application.WorksheetFunction.CountIf(Range("B1:B" & thisRow - 1), fundAccount) = 0
                corpValue = corpValue + Application.WorksheetFunction.SumIfs(Range("C1:C" & thisRow - 1), Range("B1:B" & thisRow - 1), fundAccount, Range("D1:D" & thisRow - 1), "Corp")
                indValue = indValue + Application.WorksheetFunction.SumIfs(Range("C1:C" & thisRow - 1), Range("B1:B" & thisRow - 1), fundAccount, Range("D1:D" & thisRow - 1), "Ind")
                fundAccount = Range("A" & Application.WorksheetFunction.Match(fundAccount, Range("B1:B" & thisRow - 1), 0)).Value
            Loop
            
            If corpValue + indValue = 0 Then
                Cells(thisRow, "E").Value = 0
                Cells(thisRow, "F").Value = 0
            Else
                Cells(thisRow, "E").Value = corpValue / (corpValue + indValue)
                Cells(thisRow, "F").Value = indValue / (corpValue + indValue)
            End If
    End Select
Next thisRow

End Sub

WBD

 

[vds1]

Hello FormR,

Thank you for your reply. Your formula would work if the fund holds two client account investors. If the fund holds another fund then it would fail as shown in table 2.

Its more like tree structure as shown below, fund can hold multiple investor and the fund itself. You can consider node as fund and leaf as client investor. The tree could be any structure and size.

Hello WBD,

I just saw your answer and I will respond after looking into your code

 

[FormR]

I will respond after looking into your code

Hi, I think I'll wait for this response before going any further, but example data and expected results that demonstrate this tree like structure would help if you still need help after checking the other suggestion.