Extracting data from Pivot

i am struggling to find an easy way to get all the orders containing ONLY
product A and B. Here is the kind of spreadsheet i have:

Column A Column B Column C
Order# Product Quantity
00001 A 3
00001 B 4
00001 C 5
00002 A 1
00003 B 3
00003 C 1
00004 A 3
00004 B 4
00004 C 4

I want to get just the orders that contain product A and B (basically 00001
and 00004). Can't do it with my pivot without creating an additional column
and extracting the orders that contain A and B, not elegant and time
consuming. I am sure excel has something built-in that allows you to do that
quickly. Am I missing something?
thank you,

Hi Gigi,

Data, Filter, Auto Filter. Then in the Product column click on the small
black triangle, click on Custom and choose equal to A and OR equal to B

HTH

Gilles
"gigi" <[email protected]> wrote in message
news:[email protected]...
>i am struggling to find an easy way to get all the orders containing ONLY
> product A and B. Here is the kind of spreadsheet i have:
>
> Column A Column B Column C
> Order# Product Quantity
> 00001 A 3
> 00001 B 4
> 00001 C 5
> 00002 A 1
> 00003 B 3
> 00003 C 1
> 00004 A 3
> 00004 B 4
> 00004 C 4
>
> I want to get just the orders that contain product A and B (basically
> 00001
> and 00004). Can't do it with my pivot without creating an additional
> column
> and extracting the orders that contain A and B, not elegant and time
> consuming. I am sure excel has something built-in that allows you to do
> that
> quickly. Am I missing something?
> thank you,
>

hi Gilles, i tried doing this but the problem is that it filters the products
out only. I need to get the orders that contain product A AND product B. With
the auto filter i get (from the previous example):

> > Column A Column B Column C
> > Order# Product Quantity
> > 00001 A 3
> > 00001 B 4
> > 00002 A 1
> > 00003 B 3
> > 00004 A 3
> > 00004 B 4

whereas what i need to get is:

> > Column A Column B Column C
> > Order# Product Quantity
> > 00001 A 3
> > 00001 B 4
> > 00004 A 3
> > 00004 B 4
> > 00004 C 4

thanks,
gigi

"Gilles Desjardins" wrote:

> Hi Gigi,
>
> Data, Filter, Auto Filter. Then in the Product column click on the small
> black triangle, click on Custom and choose equal to A and OR equal to B
>
> HTH
>
> Gilles
> "gigi" <[email protected]> wrote in message
> news:[email protected]...
> >i am struggling to find an easy way to get all the orders containing ONLY
> > product A and B. Here is the kind of spreadsheet i have:
> >
> > Column A Column B Column C
> > Order# Product Quantity
> > 00001 A 3
> > 00001 B 4
> > 00001 C 5
> > 00002 A 1
> > 00003 B 3
> > 00003 C 1
> > 00004 A 3
> > 00004 B 4
> > 00004 C 4
> >
> > I want to get just the orders that contain product A and B (basically
> > 00001
> > and 00004). Can't do it with my pivot without creating an additional
> > column
> > and extracting the orders that contain A and B, not elegant and time
> > consuming. I am sure excel has something built-in that allows you to do
> > that
> > quickly. Am I missing something?
> > thank you,
> >
>
>
>

hi Gilles, i tried doing this but the problem is that it filters the products
out only. I need to get the orders that contain product A AND product B. With
the auto filter i get (from the previous example):

> > Column A Column B Column C
> > Order# Product Quantity
> > 00001 A 3
> > 00001 B 4

> > 00002 A 1

> > 00003 B 3

> > 00004 A 3
> > 00004 B 4

whereas what i need to get is:

> > Column A Column B Column C
> > Order# Product Quantity
> > 00001 A 3
> > 00001 B 4
> > 00001 C 5

> > 00004 A 3
> > 00004 B 4
> > 00004 C 4

all the orders containing A and B at the same time.
thanks,
gigi

"Gilles Desjardins" wrote:

> Hi Gigi,
>
> Data, Filter, Auto Filter. Then in the Product column click on the small
> black triangle, click on Custom and choose equal to A and OR equal to B
>
> HTH
>
> Gilles
> "gigi" <[email protected]> wrote in message
> news:[email protected]...
> >i am struggling to find an easy way to get all the orders containing ONLY
> > product A and B. Here is the kind of spreadsheet i have:
> >
> > Column A Column B Column C
> > Order# Product Quantity
> > 00001 A 3
> > 00001 B 4
> > 00001 C 5
> > 00002 A 1
> > 00003 B 3
> > 00003 C 1
> > 00004 A 3
> > 00004 B 4
> > 00004 C 4
> >
> > I want to get just the orders that contain product A and B (basically
> > 00001
> > and 00004). Can't do it with my pivot without creating an additional
> > column
> > and extracting the orders that contain A and B, not elegant and time
> > consuming. I am sure excel has something built-in that allows you to do
> > that
> > quickly. Am I missing something?
> > thank you,
> >
>
>
>

I don't see the logic in that, if you want ONLY A AND B you can use custom
but in your example it seems that you want C as well?
Auto filter can only customize for 2 text values if you want more you can
use the advanced filter or add a helper column and use autofilter

=OR(A2={"A","B","C"})

copy down then filter on TRUE

however if indeed you want only A and B you can use autofilter


--

Regards,

Peo Sjoblom

"gigi" <[email protected]> wrote in message
news:[email protected]...
> hi Gilles, i tried doing this but the problem is that it filters the
products
> out only. I need to get the orders that contain product A AND product B.
With
> the auto filter i get (from the previous example):
>
> > > Column A Column B Column C
> > > Order# Product Quantity
> > > 00001 A 3
> > > 00001 B 4
> > > 00002 A 1
> > > 00003 B 3
> > > 00004 A 3
> > > 00004 B 4
>
> whereas what i need to get is:
>
> > > Column A Column B Column C
> > > Order# Product Quantity
> > > 00001 A 3
> > > 00001 B 4
> > > 00004 A 3
> > > 00004 B 4
> > > 00004 C 4
>
> thanks,
> gigi
>
> "Gilles Desjardins" wrote:
>
> > Hi Gigi,
> >
> > Data, Filter, Auto Filter. Then in the Product column click on the small
> > black triangle, click on Custom and choose equal to A and OR equal to B
> >
> > HTH
> >
> > Gilles
> > "gigi" <[email protected]> wrote in message
> > news:[email protected]...
> > >i am struggling to find an easy way to get all the orders containing
ONLY
> > > product A and B. Here is the kind of spreadsheet i have:
> > >
> > > Column A Column B Column C
> > > Order# Product Quantity
> > > 00001 A 3
> > > 00001 B 4
> > > 00001 C 5
> > > 00002 A 1
> > > 00003 B 3
> > > 00003 C 1
> > > 00004 A 3
> > > 00004 B 4
> > > 00004 C 4
> > >
> > > I want to get just the orders that contain product A and B (basically
> > > 00001
> > > and 00004). Can't do it with my pivot without creating an additional
> > > column
> > > and extracting the orders that contain A and B, not elegant and time
> > > consuming. I am sure excel has something built-in that allows you to
do
> > > that
> > > quickly. Am I missing something?
> > > thank you,
> > >
> >
> >
> >

Hi

Use Custom Orders = A AND Orders = B

Regards

Roger Govier


gigi wrote:
> hi Gilles, i tried doing this but the problem is that it filters the products
> out only. I need to get the orders that contain product A AND product B. With
> the auto filter i get (from the previous example):
>
>
>>>Column A Column B Column C
>>>Order# Product Quantity
>>>00001 A 3
>>>00001 B 4
>
>
>>>00002 A 1
>
>
>>>00003 B 3
>
>
>>>00004 A 3
>>>00004 B 4
>
>
> whereas what i need to get is:
>
>
>>>Column A Column B Column C
>>>Order# Product Quantity
>>>00001 A 3
>>>00001 B 4
>>>00001 C 5
>
>
>>>00004 A 3
>>>00004 B 4
>>>00004 C 4
>
>
> all the orders containing A and B at the same time.
> thanks,
> gigi
>
> "Gilles Desjardins" wrote:
>
>
>>Hi Gigi,
>>
>>Data, Filter, Auto Filter. Then in the Product column click on the small
>>black triangle, click on Custom and choose equal to A and OR equal to B
>>
>>HTH
>>
>>Gilles
>>"gigi" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>i am struggling to find an easy way to get all the orders containing ONLY
>>>product A and B. Here is the kind of spreadsheet i have:
>>>
>>>Column A Column B Column C
>>>Order# Product Quantity
>>>00001 A 3
>>>00001 B 4
>>>00001 C 5
>>>00002 A 1
>>>00003 B 3
>>>00003 C 1
>>>00004 A 3
>>>00004 B 4
>>>00004 C 4
>>>
>>>I want to get just the orders that contain product A and B (basically
>>>00001
>>>and 00004). Can't do it with my pivot without creating an additional
>>>column
>>>and extracting the orders that contain A and B, not elegant and time
>>>consuming. I am sure excel has something built-in that allows you to do
>>>that
>>>quickly. Am I missing something?
>>>thank you,
>>>
>>
>>
>>

Piece of cake
Use the filter feature in PTs
(see attached screen shot)


+-------------------------------------------------------------------+
|Filename: Order.JPG |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=48|
+-------------------------------------------------------------------+

--
cs02000