?
Solved

macro for excel 2007 to combine data from two worksheets

Posted on 2014-10-22
3
Medium Priority
?
91 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 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 49

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

752 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