Can I select an array for a correlation based on a value in a column? STUMPED!

Hi
I am calculating multiple Correlations for long lists of data. I want the correlation based on the value in a given column. In the below example I would like a different correlation for each value in colA and the correlation will be between colC and colD. ColB lists the different people whose data are used for the correlations. In actuality there are 30 or more people for each test. In every case ColC is the same for a given value of ColB (although I doubt that matters). The number of people taking each test varies. I would like a correlation of ColC an ColD for each value of ColA.
Thanks!

Simplified version of my data

ColA ColB ColC ColD
Test1 Fred .75 1
Test1 Max .80 0
Test1 Sara .90 1
Test2 Fred .75 1
Test2 Max .80 1
Test2 Sara .90 0
....
Test100 Fred .75 1
Test100 Max .80 1
Test100 Sara .90 1

It worked amazingly! Any chance you know how I could also calculate N for each test?

I am still having trouble with the function, I thought it was working but the calculations are off, I am wondering if you could explain your choice of numbers for the offset function? Is it possible that I have placed the formula in the wrong cell? I have four columns of data, col1 is the group, col3 and col4 need to be correlated with each other based on the value for col1. I am getting some div/o errors even though when I manually do the correlations I get a value, and also I am getting different calculations manually than the formula provides. I have place the formula in colG and the name for each group in colH.

Hi
I figured it out thanks to pp's help and some trial and error
1) do a countif function to get the total number of rows for a given question in column F
2) do a countif function that adds one if the question is the same as the question in the row above, else make it one
3) sort so you only see the first row per question, then paste this formula
4) =CORREL(OFFSET(A2,0,1,F2,1),OFFSET(A2,0,2,F2,1))