Can't quite just use a pivot table for the summary I need...

Hi, I have about 150,000 rows of lab sample data for a project. Each row contains one sample parameter, and there are 15-25 sample parameters per overall sample. Each sample parameter has a status (Normal, Caution, or Critical) assigned to it.

Now, the problem is that the overall sample takes on the status of the most severe sample parameter. There is, however, no separate entry for the overall sample. I need a count of the number of Normal, Caution, and Critical overall samples for each month in history. I can easily use a pivot table to summarize the number of sample parameters, but I have no idea how to get excel to analyze the status of each sample parameter within each overall sample, and then only count the most severe one.

Attached is an example with the data that I have, and what I need to accomplish, along with a few more clarifying comments. Note that I did this example on my computer at home with Excel 2008 for Mac, but I have Excel 2010 on Windows 7 at work. I know my way around Excel's functions very well, but don't know VBA, so I was considering using a CSV with a little C program to do this. The problem is that I don't know if the permissions on my work computer will even let me run a C program.

Thanks in advance for any help!

Hi FDibbins,

That solution essentially did the same thing as the pivot table. It didn't group the sample parameters into samples like I needed. But it's fine, I figured out a little bit of a roundabout solution with some helper columns and got it down to the pivot table that I needed to generate.

Thanks.