ExcelBanter - Extracting data from Pivot

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Extracting data from Pivot (https://www.excelbanter.com/excel-discussion-misc-queries/56928-extracting-data-pivot.html)

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,

Extracting data from Pivot

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" wrote in message
...
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,

Extracting data from Pivot

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" wrote in message
...
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,

Extracting data from Pivot

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" wrote in message
...
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,

Extracting data from Pivot

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" wrote in message
...
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" wrote in message
...
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,

Extracting data from Pivot

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" wrote in message
...

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)