Solved

Excel 2007 Compare two Worksheets for Common Data and have Excel copy and paste info from one to the other

Posted on 2011-03-24
3
799 Views
Last Modified: 2013-11-05
I have an Excel file with two worksheets that contain some common data but not the same number of rows and not necessarily in the same order.  I need to match info from the two sheets and copy info from one to the other.  One of the worksheet has 3003 rows and the other has 2565 rows so I really don’t want to do this manually.  How can I have Excel check two worksheets for common data, and then copy information from one of the sheets to the other? I am working in Excel 2007.  Here is the information on the two sheets and on what I actually need to do:

Sheet one
Column A contains Part Numbers
Column B is Blank
Column C is Blank
Column D is Blank
Column E is Blank

Sheet two
Column A contains Part Numbers
Column B contains Manufacturers
Column C contains Cross Reference Info
Column D contains Manufacturers
Column E contains Cross Reference Info


I need excel to check Column A of Sheet one against Column A of Sheet two; When it finds common data (the same part number), I need it to copy the information from columns B,C,D,E of Sheet two and paste it into columns B,C,D,E of Sheet one.

Because the information in the real file is confidential, I have created a sample file that doesn't show the actual data.  Can anyone help me with a formula or Macro that will do this?  I have very little time to get this done and it’s urgent for my company.
 Example-for-Experts-Exchange.xlsx
0
Comment
Question by:carilou
3 Comments
 
LVL 22

Accepted Solution

by:
rspahitz earned 250 total points
ID: 35207807
this is a job for vlookup:

cell B1 formula =VLOOKUP(A1, Sheet2!A:E, 2, FALSE)
cell C1 formula =VLOOKUP(A1, Sheet2!A:E, 3, FALSE)
cell D1 formula =VLOOKUP(A1, Sheet2!A:E, 4, FALSE)
cell E1 formula =VLOOKUP(A1, Sheet2!A:E, 5, FALSE)

Then copy the formulas down
0
 
LVL 6

Assisted Solution

by:KnutsonBM
KnutsonBM earned 250 total points
ID: 35207830
vlookup is what you are looking for a i believe, see attached
with-vlookup.xls
0
 

Author Closing Comment

by:carilou
ID: 35208079
Thank you so much!  You were both extremely helpful!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
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…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

758 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

19 Experts available now in Live!

Get 1:1 Help Now