Pivot table and organizing data

[rixmix]

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.

 

[txilya]

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.