Solved

macro for excel 2007 to combine data from two worksheets

Posted on 2014-10-22
3
84 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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel - Row Height +1 VBA 2 23
Manipulate Data Quickly 2 13
+1 to an even value 4 8
Formula to convert ranked data 2 6
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now