Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
"table of contents" senario | Excel Worksheet Functions | |||
Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions | |||
about senario | New Users to Excel | |||
How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel |