Solved

macro for excel 2007 to combine data from two worksheets

Posted on 2014-10-22
3
86 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

895 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

11 Experts available now in Live!

Get 1:1 Help Now