Creating a new table from another - filtering out unwanted data

[Kamalii]

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!

 

[Eva]

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]

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?

 

[Teethless mama]

How about Advanced Filter, will it work for you?

 

[Kamalii]

I don't think that solves the problem of moving this data from sheet1 to
sheet2, while automatically filtering it.

 

[Eva]

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

 

[Max]

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,SMALL($B:$B,ROWS($1:1))))
Copy C2 to D2. Select B22, 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]

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

 

[Ashish Mathur]

Hi,

You may refer to question 7 at the following link -
http://ashishmathur.com/knowledgebaseII.aspx