Combine Multiple Worksheets

H

Havenstar

I am looking to combine data from two worksheets into one. The data is being
copied in from (2) different data sources so the column structure is not the
same, but the sheets have one common column that I would like to pull data
for. For example:

Sheet 1
A B
Part Number Description

Sheet 2
A B C
PO Number Part Number Price

Is it possible to to combine on one sheet if the Part Number is the same
pull in description, PO number and price?

Thanks in advance for any suggestions,
Havenstar
 
M

Michael Conroy

Yes you can combine both sheets but it is involved. If you had any experience
with Access I would tell you to import both sheets into Access and do a
query. The entire process would take less than five minutes. The Excel answer
is longer.

To begin with, you need a column of unique part numbers. First sort both
columns of part numbers in acending order and make sure they are the left
most columns. Then copy and paste the two lists into the same column of a
third sheet and do a data query unique and paste the results in a fourth
sheet and sort this list in acending order also. Now in this fourth sheet you
will use =VLookup to pull the other columns. Assuming the first part number
is in cell A1, in cell B1 enter the formula =vlookup(A1,Description,2). I use
the word Description to mean the entire range of sheet 1, that is column A
and B and all the rows with information in them. You can give this area a
range name or simply highlight the area when prompted. The formula matches up
the part number in sheet 4 with the part number in sheet 1, finds a match,
and takes the value in column 2, which is the description. In Sheet 2 you
must have part number in column A, and assuming Price is in B and PO in C,
then giving all three columns a range name of Price, the formula in C1 would
be =vlookup(A1,Price,2) for prices and =vlookup(A1,Price,3) for PO numbers.
Hope that helps
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top