| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a new table from another - filtering out unwanted data
Here is my table for sheet1:
Shipment No. Product No. of Pieces A pencil 1 B pen 1 A eraser 1 A paper clip 2 B stamp 3 C ruler 1 B calculator 2 I am trying to create a new table in a new sheet (sheet2) that will list all the products and pieces that were part of shipment no. A This is what I would like it to look like Product No. of Pieces pencil 1 eraser 1 paper clip 2 How do I go about doing this? Please help! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a new table from another - filtering out unwanted data
Hi
The best and most flexible way is to create a pivot table. Data/Pivot table report/next/highlight your range - the best way is to highlight all columns/next/layout/drag "Product" to row and No. of Pieces to data (make sure that it shows you sum)/OK/finish -- Please click "yes" if this post helped you! Greatly appreciated Eva "Kamalii" wrote: Here is my table for sheet1: Shipment No. Product No. of Pieces A pencil 1 B pen 1 A eraser 1 A paper clip 2 B stamp 3 C ruler 1 B calculator 2 I am trying to create a new table in a new sheet (sheet2) that will list all the products and pieces that were part of shipment no. A This is what I would like it to look like Product No. of Pieces pencil 1 eraser 1 paper clip 2 How do I go about doing this? Please help! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a new table from another - filtering out unwanted dat
Thank you for your quick response.
However, I may have over simplified my example. I actually have several columns that need to be transferred over. I'm not looking to summarize the data (eg. sum of the number of pieces), just report a filtered list. Pivot table works for my simple example, but not the real life more complicated table that I'm actually dealing with. Is there another way? "Eva" wrote: Hi The best and most flexible way is to create a pivot table. Data/Pivot table report/next/highlight your range - the best way is to highlight all columns/next/layout/drag "Product" to row and No. of Pieces to data (make sure that it shows you sum)/OK/finish -- Please click "yes" if this post helped you! Greatly appreciated Eva "Kamalii" wrote: Here is my table for sheet1: Shipment No. Product No. of Pieces A pencil 1 B pen 1 A eraser 1 A paper clip 2 B stamp 3 C ruler 1 B calculator 2 I am trying to create a new table in a new sheet (sheet2) that will list all the products and pieces that were part of shipment no. A This is what I would like it to look like Product No. of Pieces pencil 1 eraser 1 paper clip 2 How do I go about doing this? Please help! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a new table from another - filtering out unwanted dat
How about Advanced Filter, will it work for you?
"Kamalii" wrote: Thank you for your quick response. However, I may have over simplified my example. I actually have several columns that need to be transferred over. I'm not looking to summarize the data (eg. sum of the number of pieces), just report a filtered list. Pivot table works for my simple example, but not the real life more complicated table that I'm actually dealing with. Is there another way? "Eva" wrote: Hi The best and most flexible way is to create a pivot table. Data/Pivot table report/next/highlight your range - the best way is to highlight all columns/next/layout/drag "Product" to row and No. of Pieces to data (make sure that it shows you sum)/OK/finish -- Please click "yes" if this post helped you! Greatly appreciated Eva "Kamalii" wrote: Here is my table for sheet1: Shipment No. Product No. of Pieces A pencil 1 B pen 1 A eraser 1 A paper clip 2 B stamp 3 C ruler 1 B calculator 2 I am trying to create a new table in a new sheet (sheet2) that will list all the products and pieces that were part of shipment no. A This is what I would like it to look like Product No. of Pieces pencil 1 eraser 1 paper clip 2 How do I go about doing this? Please help! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a new table from another - filtering out unwanted dat
I don't think that solves the problem of moving this data from sheet1 to
sheet2, while automatically filtering it. "Teethless mama" wrote: How about Advanced Filter, will it work for you? "Kamalii" wrote: Thank you for your quick response. However, I may have over simplified my example. I actually have several columns that need to be transferred over. I'm not looking to summarize the data (eg. sum of the number of pieces), just report a filtered list. Pivot table works for my simple example, but not the real life more complicated table that I'm actually dealing with. Is there another way? "Eva" wrote: Hi The best and most flexible way is to create a pivot table. Data/Pivot table report/next/highlight your range - the best way is to highlight all columns/next/layout/drag "Product" to row and No. of Pieces to data (make sure that it shows you sum)/OK/finish -- Please click "yes" if this post helped you! Greatly appreciated Eva "Kamalii" wrote: Here is my table for sheet1: Shipment No. Product No. of Pieces A pencil 1 B pen 1 A eraser 1 A paper clip 2 B stamp 3 C ruler 1 B calculator 2 I am trying to create a new table in a new sheet (sheet2) that will list all the products and pieces that were part of shipment no. A This is what I would like it to look like Product No. of Pieces pencil 1 eraser 1 paper clip 2 How do I go about doing this? Please help! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a new table from another - filtering out unwanted dat
Hi
Can you show us how it is really looks like and how should be presented in the other sheet? It is very hard to figure out what to do when you don't see the details -- Greatly appreciated Eva "Kamalii" wrote: Thank you for your quick response. However, I may have over simplified my example. I actually have several columns that need to be transferred over. I'm not looking to summarize the data (eg. sum of the number of pieces), just report a filtered list. Pivot table works for my simple example, but not the real life more complicated table that I'm actually dealing with. Is there another way? "Eva" wrote: Hi The best and most flexible way is to create a pivot table. Data/Pivot table report/next/highlight your range - the best way is to highlight all columns/next/layout/drag "Product" to row and No. of Pieces to data (make sure that it shows you sum)/OK/finish -- Please click "yes" if this post helped you! Greatly appreciated Eva "Kamalii" wrote: Here is my table for sheet1: Shipment No. Product No. of Pieces A pencil 1 B pen 1 A eraser 1 A paper clip 2 B stamp 3 C ruler 1 B calculator 2 I am trying to create a new table in a new sheet (sheet2) that will list all the products and pieces that were part of shipment no. A This is what I would like it to look like Product No. of Pieces pencil 1 eraser 1 paper clip 2 How do I go about doing this? Please help! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a new table from another - filtering out unwanted dat
Here's a simple way to set it up ...
Assume your source data as posted is in Sheet1, cols A to C, data from row2 down, where col A = shipment # In another sheet, eg Sheet2 Assume A2 will house the shipment no. eg: A In B2: =IF(A$2="","",IF(Sheet1!A2=A$2,ROW(),"")) In C2: =IF(ROWS($1:1)COUNT($B:$B),"",INDEX(Sheet1!B:B,SM ALL($B:$B,ROWS($1:1)))) Copy C2 to D2. Select B2:D2, copy down to cover the max expected extent of source data, eg down to D100. Minimize col B. The expected results for the input in A2 will be returned in cols C and D, all neatly packed at the top. Changing the input in A2 to say: B will return the associated lines. And if you need more enquiry sheets to use simultaneously for various shipment nos, just make copies of Sheet2. Success? hit YES below. -- Max Singapore --- "Kamalii" wrote: I don't think that solves the problem of moving this data from sheet1 to sheet2, while automatically filtering it. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a new table from another - filtering out unwanted dat
Thought OP did illustrate what s/he wanted in that other sheet, in the
original post. I've suggested a way for OP to get it up in the other branch of this thread, but there's always room for alternatives. Do feel free to join in there with your options -- Max Singapore "Eva" wrote in message ... Can you show us how it is really looks like and how should be presented in the other sheet? It is very hard to figure out what to do when you don't see the details |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a new table from another - filtering out unwanted data
Hi,
You may refer to question 7 at the following link - http://ashishmathur.com/knowledgebaseII.aspx -- Regards, Ashish Mathur Microsoft Excel MVP "Kamalii" wrote in message ... Here is my table for sheet1: Shipment No. Product No. of Pieces A pencil 1 B pen 1 A eraser 1 A paper clip 2 B stamp 3 C ruler 1 B calculator 2 I am trying to create a new table in a new sheet (sheet2) that will list all the products and pieces that were part of shipment no. A This is what I would like it to look like Product No. of Pieces pencil 1 eraser 1 paper clip 2 How do I go about doing this? Please help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| pivot table n filtering of data | Excel Worksheet Functions | |||
| Pivot Table - Filtering Data Field | Excel Discussion (Misc queries) | |||
| Filtering Pivot Table Data | Excel Discussion (Misc queries) | |||
| Filtering Pivot Table Data | Excel Discussion (Misc queries) | |||
| Filtering data from one pivot table against another | Excel Discussion (Misc queries) | |||
Linear Mode
