Forum Discussion
Formula to return duplicate values
Hi Everyone,
I need assistance on a formula to return duplicate values that wouldn't break the formulas i currently have on the spread sheet
To summarize what the spreadsheet does :
On the first sheet the formulas pull in the fees for each stock as per the 2nd sheet(LenderDailyBilling-MAML) and splits them according to the portfolio %'s as per the 3rd sheet(SecLendingDetail)
For example :
R100 in fees for ANG , ANG is split into 2 portfolios(39% to to and 61% for the other) and then split the fees R39 to 1 and R61 to the other according to the % split.
In this instance GFI appears twice on the fee sheet(2nd sheet), and needs to be split twice, whereas my original formula only takes the first line of GFI and splits it accordingly, I'm looking for a way to amend the formula to take each GFI(or whichever stock it will be on the given day) and split each of them.
For example on the 2nd sheet GFI has 2 values : 103.23 & 28.74
According to the 3rd sheet GFI is split into 2 portfolios - MMITED 36% & MMISXD 64%
Now the current formula only takes the first GFI value of 103.23 and splits it between the portfolios but ignores the 2nd value(28.74)
Sheet attached.
Any assistance would be appreciated , thank you!
1 Reply
Wow! Not the simplest of spreadsheets. Not sure if I follow why you have all these formulae in the Fee Split tab.
Would it be possible to restructure the data in SecLendingDetail? Now you have separate rows for each possible Holding% for a individual Instrument code. I sense it would become much easier when you have one row for each Instrument code, but with separate columns for each Holding%. From your data I understand that an Instrument can have up to 3 Holding%s (thus 3 columns).
Then it will be very easy to split the Fee on the LenderDaily Billing tab for each Ticker ID by a VLOOKUP formula that picks up the valid % in your "restructured" SecLendingDetail tab.
Just a suggestion. Good luck!
