Combine data from multiple worksheets to master spreadsheet

Renny0927

New Member
Joined
Oct 25, 2014
Messages
5
I have a workbook that contains about 100 worksheets that house data I need to combine into one master file. Each of the worksheets are formatted the same with the data contained in the same column(s)/row(s). I need to take the info and create a row of information for each client. The Data column, I would like to transpose so the data is in a row and the client name would be the first column and then the last column would be Average Raw Score. I have attached a sample worksheet that contains the current layout and the layout I would like to have on a master worksheet. I tried to add an attachment but I am not allowed to on this site, no idea why.

Data resides D10:D18
Client name - B2
Average Raw Score - B33

Any help on this will be greatly appreciated!

REnny

RISK RATING MATRIXCOMPOSITE RISK RATING
Borrower Namesmith RealtyDate 8/31/2016
Business Namebusiness nameLoan Officerloan officer name
Loan #S1403
1) Enter data into Table 1 Column D (Data) and Column E (Date Reported). Score is calculated automatically.
2) Using Table 2 Average Raw Score, round up or down according to potential risk of loan.
3) Assign a composite risk rating based on a combination of quantitative and qualitative assessment.
TABLE 1 QUANTITATIVE ASSESSMENT
Credit CriteriaIndicatorSourceDataDate ReportedScore
Loan to ValueTotal Loans OutstandingDownHome$20,8698/31/201629.1%
Collateral ValueGreen collateral files$71,7501/8/2010
Credit ScoreFICO Score (Transunion)Transunion†$6832011683
Debt Service Coverage Ratio (Including Loan)Yearly Operating IncomeFinancial statements*$96,65120134.474584722
Yearly Loan PaymentsDownHome$21,6002016
Current RatioCurrent AssetsFinancial statements*$127,58212/31/20120.846
Net WorthCurrent LiabilitiesFinancial statements*$150,78012/31/2012-23198.47
Profitability (# consecutive years)# Years that (Income - Expenses) > $0Financial statements*120131
Time in Business# YearsDownHome66
† If borrower has a FICO score on file that is < 3 years old, this should be used. If their FICO score is > 3 years old, a new soft pull credit check should be run.
* Alternately tax returns, income statements and balance sheets can be used.
TABLE 2 RISK RATING MATRIX
Credit CriteriaRisk RatingRisk Rating 1Risk Rating 2Risk Rating 3
Loan to Value160% and below61%-79%80% and above
Credit Score3750-850700-749650-699
Debt Service Coverage Ratio1>1.311.21 - 1.31.1 - 1.2
Current Ratio3>1.511.11-1.501.00-1.10
Net Worth3Over $50K$25K-50K<$25K
Profitability3>3 years21
Time in Business1>5 years3-5 years<3 years
Total Raw Score15
Average Raw Score2.1
TABLE 3 QUALITATIVE ASSESSMENT
CharacterCapacityCapitalCollateralCash Flow
Current Layout per worksheet above
This is the layout I would like to have on a master
Client NameTotal Loans OutstandingCollateral ValueFICO Score (Transunion)Yearly Operating IncomeYearly Loan PaymentsCurrent AssetsCurrent Liabilities# Years that (Income - Expenses) > $0# YearsAverage Raw Score
smith realty$20,869$71,750$683$96,651$21,600$127,582$150,780162.1

<tbody>
</tbody>
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi Renny,

This code will do it for you.

It is a bit more complicated because I use arrays to read each sheet in one read operation and gather the output to write it to the output sheet in one write operation. Read and write operations are relatively slow in Excel, so if you have hundreds of sheets, doing it differently could be slow. This way it is so fast, you won't even believe it did run.

i also added some code incase there is no summary sheet, it will create one.

Read the comments to get a bit of understanding.

Let me know how it goes.

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> Summarise()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsWS <SPAN style="color:#00007F">As</SPAN> Worksheet, wsSum <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> lROut <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lRIn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lShtCnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> vOut <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, vDat <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> sSummSheetName = "Summary"    <SPAN style="color:#007F00">' <<< Summary sheet name - modify as required</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> lNumCols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 11         <SPAN style="color:#007F00">' <<< 11 columns wide (A-K). If at later stage _<br>                                              more columns added then increase this number</SPAN><br>    <br>    <br>    <SPAN style="color:#007F00">' count the number of sheets, add 1:</SPAN><br>    lShtCnt = Worksheets.Count + 1<br>    <SPAN style="color:#007F00">' size the output array. We are using an _<br>      array to make this macro blindingly fast _<br>      with only one write to the summary sheet</SPAN><br>    <SPAN style="color:#00007F">ReDim</SPAN> vOut(1 <SPAN style="color:#00007F">To</SPAN> lShtCnt, 1 <SPAN style="color:#00007F">To</SPAN> lNumCols)  <SPAN style="color:#007F00">' 11 columns wide (A-K)</SPAN><br>    lRIn = 1<br>    <br>    <br>    <SPAN style="color:#007F00">' Set the output sheet</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wsSum = Sheets(sSummSheetName)<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <br>    <SPAN style="color:#00007F">If</SPAN> wsSum <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">' no summary sheet yet, create</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> wsSum = Sheets.Add(before:=Sheets(1))<br>        <SPAN style="color:#00007F">With</SPAN> wsSum<br>            .Name = sSummSheetName<br>            .Range("A1:K1").ColumnWidth = 18<br>            .Columns("K").Font.Bold = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#007F00">' Enter the headings into the output array</SPAN><br>        vOut(1, 1) = "Client Name"<br>        vOut(1, 2) = "Total Loans Outstanding"<br>        vOut(1, 3) = "Collateral Value"<br>        vOut(1, 4) = "FICO Score (Transunion)"<br>        vOut(1, 5) = "Yearly Operating Income"<br>        vOut(1, 6) = "Yearly Loan Payments"<br>        vOut(1, 7) = "Current Assets"<br>        vOut(1, 8) = "Current Liabilities"<br>        vOut(1, 9) = "# Years that (Income - Expenses) > $0"<br>        vOut(1, 10) = "# Years"<br>        vOut(1, 11) = "Average Raw Score"<br>        lRIn = 2<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>      <br>    <SPAN style="color:#007F00">' Copy the data for each sheet to output array</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wsWS <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Sheets<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> wsWS <SPAN style="color:#00007F">Is</SPAN> wsSum <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">' read the sheet into an array for faster processing</SPAN><br>        vDat = wsWS.Range("A1:D33")<br>        <SPAN style="color:#007F00">' now A1 is vDat(1,1) and B33 is vDat(33,2)</SPAN><br>        <br><SPAN style="color:#007F00">' Client Name | Total Loans | Collateral Value |FICO Score | Yearly Op Income | Yearly Loan Paym |Current Assets | Current Liabilities | # Years(Inc - Exp) > $0 |  # Years Average Raw Score</SPAN><br>        <br>        <SPAN style="color:#007F00">' write the data into the output array</SPAN><br>        vOut(lRIn, 1) = vDat(2, 2) <SPAN style="color:#007F00">' name in B2</SPAN><br>        vOut(lRIn, 2) = vDat(10, 4) <SPAN style="color:#007F00">' Tot Loans - D4</SPAN><br>        vOut(lRIn, 3) = vDat(11, 3) <SPAN style="color:#007F00">' Coll Valu - C11</SPAN><br>        vOut(lRIn, 4) = vDat(12, 4) <SPAN style="color:#007F00">' Fic Score - D12</SPAN><br>        vOut(lRIn, 5) = vDat(13, 4) <SPAN style="color:#007F00">' yearl income - D13</SPAN><br>        vOut(lRIn, 6) = vDat(14, 3) <SPAN style="color:#007F00">' Y Loan Paym - C14</SPAN><br>        vOut(lRIn, 7) = vDat(15, 4) <SPAN style="color:#007F00">' Curr Assts - D15</SPAN><br>        vOut(lRIn, 8) = vDat(16, 4) <SPAN style="color:#007F00">' Curr Liab - D16</SPAN><br>        vOut(lRIn, 9) = vDat(17, 4) <SPAN style="color:#007F00">' Y >$0 - D17</SPAN><br>        vOut(lRIn, 10) = vDat(18, 4) <SPAN style="color:#007F00">' #Y - D18</SPAN><br>        vOut(lRIn, 11) = vDat(33, 2) <SPAN style="color:#007F00">' Score B33</SPAN><br>        <SPAN style="color:#007F00">' increment row counter</SPAN><br>        lRIn = lRIn + 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> wsWS<br>    <br>        <SPAN style="color:#007F00">' Get 1st empty row on Summary sheet</SPAN><br>    lROut = wsSum.Cells(Rows.Count, 1).End(xlUp).Row + 1<br>    <SPAN style="color:#007F00">' Write the output to the summary sheet</SPAN><br>    wsSum.Cells(lROut, 1).Resize(lShtCnt, lNumCols).Value = vOut<br><br>    <SPAN style="color:#007F00">' clean up</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wsSum = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top