Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine Multiple Worksheets
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine Multiple Worksheets
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 -- Michael Conroy Stamford, CT "Havenstar" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine Multiple Worksheets
This assumes you have multiple POs
for some part numbers. Pivot Table, VBA used. No formulas used. Excel 2003. http://www.freefilehosting.net/download/3b2ga |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) | |||
Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) | |||
How do I combine lists in multiple worksheets | Excel Worksheet Functions | |||
combine multiple worksheets & files into 1 | Excel Worksheet Functions | |||
how can I combine multiple worksheets into one? | Excel Worksheet Functions |