Solved

macro for excel 2007 to combine data from two worksheets

Posted on 2014-10-22
3
87 Views
Last Modified: 2014-11-25
I have two different worksheets (Sheet1 and Sheet2) with columns and data. Sheet1 has 57 columns and Sheet2 has 62 columns. Some of the columns are the same in both worksheets while each worksheet as some different columns or slighly different column names iwth similiar data.

Is it possible to create a macro to merge the two sheets of data into one report still? Below are the column header names within the 2 sheets.

Sheet1 - columns

QualifyingScore
BorrFirstName
BorrLastName
BorrTotalMonthlyIncome
CoBorrTotalMonthlyIncome
TotalLoanAmount
LoanAmount
LTV
CLTV
DocType
IsImpound
NoteRate
LoanType
LoanNumber
Term
LoanStatus
SubjPropPurpose
LoanProgramName
RegisteredComments
SubjPropAddress
SubjPropCity
SubjPropState
SubjPropZip
SubjPropCounty
PropertyType
ProductCode
PurchasePrice
LoanPurpose
PurposeofRefinance
SubjPropNoOfUnits
902MortgageInsurancePremium
WarehouseFunder
RateLockedDate
LockExpirationDate
BrokerRateLockPrice
InvestorRateSheetID
BranchName
AssignedLoanOfficerName
CanceledDate
EstimatedCloseDate
InvestorBaseRateLockPrice
InvestorRateLockPrice
InvestorRateLockInvestorName
InvestorRateLockDate
InvestorRateLockConfirmation#
InvestorRateLockLoan#
PurchaseAdviceTotalPrice
FundedDate
LoanPurchasedDate
InvestorCommitmentType
DTI
AUSfindingspulled
AUResponse
IsBorrSelf-Employed
MortgageInsuranceProvider
RejectedDate
BrokerRateLockInvestorPrice


Sheet2 - Columns

Amortization Term
Amortization Type
ARM Index
Borrower Funding Date
Borrower Lock Date
Borrower Lock Expiration Date
Borrower Name
Branch
Broker
Broker Buy Price
Cancellation Date
Commitment Sell Price
Credit Grade
Disbursement Date
Interest Rate
Investor Lock Date
Investor Purchase Price
Loan Amount
Loan Number
Loan Officer
Loan Program
Loan State
Loan Status
Loan Type
Origination Type
Address
Borrower Lock Term
Buydown
Cash-out
CLTV
Collecting Escrows?
Committed Investor
Designated Investor
Doc Type
Estimated Closing Date
FICOs
Investor Funded Date
Investor Lock Expiration Date
Loan Purpose
LTV
Occupancy
Property Type
Underwriting
Investor
Zip Code
ARM Caps
ARM Change Date
Borrower Application Date
Coborrower Name
Ratios
Best Efforts Price
SRP
Feature Adjustment
Borrower Monthly Income
Coborrower Monthly Income
City
Terrace Closed Loan Gain
County
Investor Purchase Date
Investor Commitment Number
FHA Base Loan Amount
Final Gain
0
Comment
Question by:Terrace
3 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40398162
Short Answer: Yes
Long Answer:  Since only two fields (LTV, CLTV) are explicitly in both lists of column names, they're the only obvious two matches.  Others like "LoanStatus" and "Loan Status" are intuitively obvious, but not to a computer.  

First, you would need to create an indexing table that identifies what columns are equivalent and, by extension, which ones are unique.  Second, you would need to indicate what column(s) identifies unique rows/records of data between the two sheets so that records can be combined.  I'm going to guess that Loan Number would be that unique value, but you'll need to determine that.

Once both of these tasks are complete, it should be possible to merge the two sets of data onto a new sheet with all the columns/values shown.

-Glenn
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40464081
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question