Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
combining multiple columns from multiple files
Here's my situation. I have 3 files covering 3 years (2004, 2005 and 2006).
Each contains 3 columns columns of information. Column A is Part Number. Collumn B is Description. Column C is Quantity (sold that year). I'm trying to determine the Min/Max levels we need to stock for each item. Each year there are different part numbers for a variety of reasons. Some discontinued, some new that year etc. Basically, I want a sheet with columns that looks like this: Part# Desc 04usage 05usage 06usage Once I have a sheet that contains this, I can manage the rest. Or is there a way to make a new sheet that draws the information from the 3 sheets? Is this possible... and possible in a way that a noob like me can manage? I got "volunteered" to do this, and for the life of me, can't understand why. Thanks! John |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
combining multiple columns from multiple files
OK, do do it the way you want, in cell C2 (your 04usage column) type:
=VLOOKUP(A2,04filesheet1A:C,3,FALSE) in the 05 and 06 columns, same exact formula, just change the workbook you're using. or, in C2, =MAX(VLOOKUP(A2,04filesheet1A:C,3,FALSE), same with workbook 05, same with workbook 06) Hope that made sense. "osiris73" wrote: Here's my situation. I have 3 files covering 3 years (2004, 2005 and 2006). Each contains 3 columns columns of information. Column A is Part Number. Collumn B is Description. Column C is Quantity (sold that year). I'm trying to determine the Min/Max levels we need to stock for each item. Each year there are different part numbers for a variety of reasons. Some discontinued, some new that year etc. Basically, I want a sheet with columns that looks like this: Part# Desc 04usage 05usage 06usage Once I have a sheet that contains this, I can manage the rest. Or is there a way to make a new sheet that draws the information from the 3 sheets? Is this possible... and possible in a way that a noob like me can manage? I got "volunteered" to do this, and for the life of me, can't understand why. Thanks! John |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
combining multiple columns from multiple files
You should utilize the Data - Pivot Table Command
Select your worksheet range - make sure you include column headings Then click on layout Drag the columns that you need into the Row column which in your case would be your part number Drag each year into the column header Then Drag each each year into the Data box - double click on each year to change it from "Count" to "Sum" Click Finish when you are done and Excel will create a new worksheet with your data. "osiris73" wrote: Here's my situation. I have 3 files covering 3 years (2004, 2005 and 2006). Each contains 3 columns columns of information. Column A is Part Number. Collumn B is Description. Column C is Quantity (sold that year). I'm trying to determine the Min/Max levels we need to stock for each item. Each year there are different part numbers for a variety of reasons. Some discontinued, some new that year etc. Basically, I want a sheet with columns that looks like this: Part# Desc 04usage 05usage 06usage Once I have a sheet that contains this, I can manage the rest. Or is there a way to make a new sheet that draws the information from the 3 sheets? Is this possible... and possible in a way that a noob like me can manage? I got "volunteered" to do this, and for the life of me, can't understand why. Thanks! John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting different multiple columns | Excel Worksheet Functions | |||
Multiple Columns to Single Columns | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combining Data from Multiple Columns | Excel Worksheet Functions |