Updating multiple workbooks to one workbook

[vijrana]

Hi,

I have a attached the word document (explaining my query and excel files ) input excel files- 60+deliquent , portfolio
output excel file - final


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