Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot table and organizing data
This one is really making me scratch my head. Here is the story. I have a
list of information which I am pulling in via a query from SQL. Data is good and it correctly comes into Excel (03 or 07). I have five columns with data: Date, Time, AccountID , Status. First two are self-explanatory; third is a 3-letter ID, forth is a status (pass/fail). Ok, now that you have an idea, here is what I need: 1. List the account IDs as a column 2. List the dates as rows 3. Place the alert into the location that corresponds to the appropriate data and account I know this 'sounds' like a straight-forward pivot table. However, the issue comes in with the alert. It is text and not a number. As you know the 'inside' area of a pivot table is looking for numbers AND wants to do some type of calculation. I could convert the alert field to being a '0' for pass and a '1' for fail in order to overcome this limit. However, I cannot figure out how to tell Excel to not perform a calculation on these cells, leaving them as their original values. Looking for some expertise. This is driving me absolutely crazy. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot table and organizing data
Hi rixmix,
Yes - convert status to 1 and 0 and let Excel calculate Max or Min for source field Status. Let's assume that in your list you have two entries with the same Date and same AccountID, but with different Time and Status - 0 (pass) and 1 (fail). If based on the two entries for that Date & AccountID you consider Status to be 0 (pass), in your pivot table select Summarize by Min for source field Status. If you want Status to be 1 (fail), select Summarize by Max. Hope it helps. "rixmix" wrote: This one is really making me scratch my head. Here is the story. I have a list of information which I am pulling in via a query from SQL. Data is good and it correctly comes into Excel (03 or 07). I have five columns with data: Date, Time, AccountID , Status. First two are self-explanatory; third is a 3-letter ID, forth is a status (pass/fail). Ok, now that you have an idea, here is what I need: 1. List the account IDs as a column 2. List the dates as rows 3. Place the alert into the location that corresponds to the appropriate data and account I know this 'sounds' like a straight-forward pivot table. However, the issue comes in with the alert. It is text and not a number. As you know the 'inside' area of a pivot table is looking for numbers AND wants to do some type of calculation. I could convert the alert field to being a '0' for pass and a '1' for fail in order to overcome this limit. However, I cannot figure out how to tell Excel to not perform a calculation on these cells, leaving them as their original values. Looking for some expertise. This is driving me absolutely crazy. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help organizing data using Pivot Table | Excel Discussion (Misc queries) | |||
Organizing worksheets in a workbook | New Users to Excel |