Excel vba macro
- or -
Post a project like this4066
$$
- Posted:
- Proposals: 1
- Remote
- #217920
- Awarded
Description
Experience Level: Intermediate
Steps to be performed
Open workbook1 ( 60 + dq loans) has n number of families
step 1
first sort data from top to bottom on the basis of current balance for each family
for example
family Deal id Loan structure Current balance date
hotel 1 Fma 30 2/11/2013
rest 3 fm 15 2/12/2013
bar 3 fm 5 2/13/2013
hotel 1 fmb 20 2/18/2013
bar 4 fm 6 2/14/2013
hotel 2 fmc 30 2/15/2013
hotel 2 fmd 10 2/16/2013
Hotel total current balance is 30+20+30+10= 90
Rest= 15
Bar= 5+6= 11
After sorting output would be like
family Deal id Loan structure Current balance date
hotel 1 Fma 30 2/11/2013
hotel 1 fmb 20 2/18/2013
hotel 2 fmc 30 2/15/2013
hotel 2 fmd 10 2/16/2013
rest 3 fm 15 2/12/2013
bar 3 fm 6 2/13/2013
bar 4 fm 5 2/14/2013
Now there is a portfolio worksheet
Which has deal id , cusip,market value, portfolio
Portfolio tab
Deal id 1 cusip portfolio Market value
1 a peter 100
1 b england 200
2 c louis 300
Deal id 1 cusip portfolio Market value
3 d van 500
3 e huesen 900
4 f allen 1000
Note cusip is unique
Final workbook (top 5 60+ dq will be ) output
for deal id 1 it is reported twice in portfolio worksheet it means deal id 1 has two cusip ,two portfolio and two market value hence it is repoted twice for hotel lying in deal id 1
For deal id 2 we have only one cusip, portfolio and market value so it is reported only once
Family deal Loan structure Current balance cusip portfolio Market value date
hotel 1 Fma 30 a peter 100 2/11/2013
hotel 1 Fma 30 b england 200 2/11/2013
hotel 1 fmb 20 a peter 100 2/18/2013
hotel 1 fmb 20 b england 200 2/18/2013
hotel 2 fmc 30 c louis 300 2/15/2013
hotel 2 fmd 10 c louis 300 2/16/2013
rest 3 fm 15 d van 500 2/12/2013
rest 3 fm 15 e huesen 900 2/12/2013
bar 3 fm 6 d van 500 2/13/2013
bar 3 fm 6 e huesen 900 2/13/2013
bar 4 fm 5 f allen 1000 2/14/2013
The one marked with red is first family in the sorted list yellow is second and green is third
From top to bottom this give top 3 family on basis of current balance
Open workbook1 ( 60 + dq loans) has n number of families
step 1
first sort data from top to bottom on the basis of current balance for each family
for example
family Deal id Loan structure Current balance date
hotel 1 Fma 30 2/11/2013
rest 3 fm 15 2/12/2013
bar 3 fm 5 2/13/2013
hotel 1 fmb 20 2/18/2013
bar 4 fm 6 2/14/2013
hotel 2 fmc 30 2/15/2013
hotel 2 fmd 10 2/16/2013
Hotel total current balance is 30+20+30+10= 90
Rest= 15
Bar= 5+6= 11
After sorting output would be like
family Deal id Loan structure Current balance date
hotel 1 Fma 30 2/11/2013
hotel 1 fmb 20 2/18/2013
hotel 2 fmc 30 2/15/2013
hotel 2 fmd 10 2/16/2013
rest 3 fm 15 2/12/2013
bar 3 fm 6 2/13/2013
bar 4 fm 5 2/14/2013
Now there is a portfolio worksheet
Which has deal id , cusip,market value, portfolio
Portfolio tab
Deal id 1 cusip portfolio Market value
1 a peter 100
1 b england 200
2 c louis 300
Deal id 1 cusip portfolio Market value
3 d van 500
3 e huesen 900
4 f allen 1000
Note cusip is unique
Final workbook (top 5 60+ dq will be ) output
for deal id 1 it is reported twice in portfolio worksheet it means deal id 1 has two cusip ,two portfolio and two market value hence it is repoted twice for hotel lying in deal id 1
For deal id 2 we have only one cusip, portfolio and market value so it is reported only once
Family deal Loan structure Current balance cusip portfolio Market value date
hotel 1 Fma 30 a peter 100 2/11/2013
hotel 1 Fma 30 b england 200 2/11/2013
hotel 1 fmb 20 a peter 100 2/18/2013
hotel 1 fmb 20 b england 200 2/18/2013
hotel 2 fmc 30 c louis 300 2/15/2013
hotel 2 fmd 10 c louis 300 2/16/2013
rest 3 fm 15 d van 500 2/12/2013
rest 3 fm 15 e huesen 900 2/12/2013
bar 3 fm 6 d van 500 2/13/2013
bar 3 fm 6 e huesen 900 2/13/2013
bar 4 fm 5 f allen 1000 2/14/2013
The one marked with red is first family in the sorted list yellow is second and green is third
From top to bottom this give top 3 family on basis of current balance
Vijendra R.
0% (0)Projects Completed
-
Freelancers worked with
-
Projects awarded
100%
Last project
5 May 2024
India
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies