Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Isoloating particular senario from pivot table

Hi.

Here's the overview of what I am trying to do. I have a spreadsheet with 7k+
order request that have been placed in our system. Each record has the
request information:
order #;
request date;
requestor (name);
associated account #(number);
and request type(text a, b, c, d).
Each account has multiple requests, different requestors, and possible
different request types. I am trying to isolate the accounts that had both
type request a and c.

My first step was to do a pivot table. Here's my layout:
Row=Account #
Column=Request type
Data=Count of Order
This works well for letting me see how many orders were placed under each
request type for each plan. It also lets me drill down to the 3 orders of
type a, and 5 of type c, or the total 8 of all types on my original data. I
don't want to loose this capability because of the story it tells for each
account and the people involved.

However, the pivot table still has 3k+ accounts with order made this year.
My second step was a nested IF statement, but I can't figure out how to get
it looking to each row, instead of the GETPIVOTDATA $A$3.

Is there a way I can isolate my view to just the accounts with orders placed
for both types a and c (i.e. if they did either or, I don't need to see it;
just both)?

Any advice or assistance would be appreciated greatly.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default Isoloating particular senario from pivot table

Hi,

You've showed us what you have, but not how its laid out:

order date name acct type
1 11/11/2008 shane 21 a
b
c
d
2 11/11/2008 bill 31 b
c
d
3 11/11/2008 shane 21 c

or

acct order date name type
21 1 11/11/2008 shane a
21 1 11/11/2008 shane b
21 1 11/11/2008 shane c
21 1 11/11/2008 shane d
21 3 1/1/2008 shane c
21 3 1/1/2008 mary a
31 2 11/11/2008 bill b
31 2 11/11/2008 bill c
31 2 11/11/2008 bill d

or something completely different? This is the raw data area I'm talking
about.
--
Thanks,
Shane Devenshire


"Christi" wrote:

Hi.

Here's the overview of what I am trying to do. I have a spreadsheet with 7k+
order request that have been placed in our system. Each record has the
request information:
order #;
request date;
requestor (name);
associated account #(number);
and request type(text a, b, c, d).
Each account has multiple requests, different requestors, and possible
different request types. I am trying to isolate the accounts that had both
type request a and c.

My first step was to do a pivot table. Here's my layout:
Row=Account #
Column=Request type
Data=Count of Order
This works well for letting me see how many orders were placed under each
request type for each plan. It also lets me drill down to the 3 orders of
type a, and 5 of type c, or the total 8 of all types on my original data. I
don't want to loose this capability because of the story it tells for each
account and the people involved.

However, the pivot table still has 3k+ accounts with order made this year.
My second step was a nested IF statement, but I can't figure out how to get
it looking to each row, instead of the GETPIVOTDATA $A$3.

Is there a way I can isolate my view to just the accounts with orders placed
for both types a and c (i.e. if they did either or, I don't need to see it;
just both)?

Any advice or assistance would be appreciated greatly.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Isoloating particular senario from pivot table

Here's an example:
Acct Type Requestor Request Date Order#
1 A Shane 1/1/08 12345
1 B Shane 2/1/08 12346
1 B Shane 4/1/08 12346
1 C Shane 7/1/08 12347
2 A Shane 1/1/08 12348
2 B Claire 8/1/08 12349
2 A Claire 9/1/08 12350

Does this help?

"ShaneDevenshire" wrote:

Hi,

You've showed us what you have, but not how its laid out:

order date name acct type
1 11/11/2008 shane 21 a
b
c
d
2 11/11/2008 bill 31 b
c
d
3 11/11/2008 shane 21 c

or

acct order date name type
21 1 11/11/2008 shane a
21 1 11/11/2008 shane b
21 1 11/11/2008 shane c
21 1 11/11/2008 shane d
21 3 1/1/2008 shane c
21 3 1/1/2008 mary a
31 2 11/11/2008 bill b
31 2 11/11/2008 bill c
31 2 11/11/2008 bill d

or something completely different? This is the raw data area I'm talking
about.
--
Thanks,
Shane Devenshire


"Christi" wrote:

Hi.

Here's the overview of what I am trying to do. I have a spreadsheet with 7k+
order request that have been placed in our system. Each record has the
request information:
order #;
request date;
requestor (name);
associated account #(number);
and request type(text a, b, c, d).
Each account has multiple requests, different requestors, and possible
different request types. I am trying to isolate the accounts that had both
type request a and c.

My first step was to do a pivot table. Here's my layout:
Row=Account #
Column=Request type
Data=Count of Order
This works well for letting me see how many orders were placed under each
request type for each plan. It also lets me drill down to the 3 orders of
type a, and 5 of type c, or the total 8 of all types on my original data. I
don't want to loose this capability because of the story it tells for each
account and the people involved.

However, the pivot table still has 3k+ accounts with order made this year.
My second step was a nested IF statement, but I can't figure out how to get
it looking to each row, instead of the GETPIVOTDATA $A$3.

Is there a way I can isolate my view to just the accounts with orders placed
for both types a and c (i.e. if they did either or, I don't need to see it;
just both)?

Any advice or assistance would be appreciated greatly.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to create pivot table from existing pivot table in excel 2007 Udayraj Dhulekar Excel Discussion (Misc queries) 2 July 8th 13 08:22 PM
"table of contents" senario RGlade Excel Worksheet Functions 7 January 29th 08 03:06 PM
Filter lines containing pivot table and non pivot table data Grover Excel Worksheet Functions 0 September 24th 07 07:20 PM
about senario hassan barjini New Users to Excel 2 April 24th 06 06:31 PM
How do I create a pivot table if the pivot table icon or menu ite. Lynn@WS Charts and Charting in Excel 1 December 16th 04 03:36 AM


All times are GMT +1. The time now is 11:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"