data query

[minnebelle]

Samples, subjects, and test results are summarized on a spreadsheet. Each
subject (column B) has up to 5 samples (donor, baseline, 3 month, 6 month, 1
year). Each sample has a unique sampleID (column A), visit type (column C)
and date collected (column D). Test results for each sample are in the
columns to the right (columns E and greater). A subject might not have all
samples drawn for a variety of reasons.

In general I want to be able to select subjects who meet certain criteria as
to the number or type(visit) of samples that have been collected so I can do
further analysis on those samples or subjects. I only want those subjects who
have a certain number of samples or only those who have a baseline sample so
I can compare followup samples. I think the process will be relatively
similar for any of these but I can't figure our where to start.

For example, how can I filter/sort/query so as to analyze a subset of the
samples with the following characteristics:

All subjects who have a baseline and a 3 month sample

All subjects who have at least 3 samples (doesn't matter which ones)

All subjects who have a donor sample and at least 2 more samples

All subjects who have completed the study (reached 1 year), have a baseline
sample and at least 2 more samples

thanks for the help!

 

[rsenn]

Minnebelle,

What you are asking certainly looks possible. I think I can set up
the formulas to lead you to your answers, but to make sure that I've
got your data and structure nailed, could you send me a sample
spreadsheet with realistic, even if sanitized, data?

Randall dot Senn
at excite dot com