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.
0
rixmix (1)
10/5/2006 12:16:01 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
479 Views

Similar Articles

[PageSpeed] 57

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.
0
txilya (1)
10/19/2006 4:16:01 AM
Reply:

Similar Artilces: