I have a problem with re-structuring data in excel for the analysis I need to do. I basically get my data in the following format from the database:
Client Identifier for Mr A.- Date of assessment - Summary score 1 from assessment - Summary score 2 from assessment - Summary score 3 from assessment - etc.
Client Identifier for Mr A. - Second assessment date - Summary score 1 from this second assessment - summary score 2 from this second assessment - summary score 3 from this second assessment -- etc.
Client identifier for Mr B. - Date of assessment ...
This goes on and on. And to make matters more complicated, Mr A may be assessed at three different time points (typically 30 days apart) and Mr B may be assessed 6 times, and Mr. C may be assessed once, etc. So the amount of times is not consistent.
And I am trying to make graphs that will have the average of all of the summary assessment scores at time 1, and then average each score at all second assessments, and then average score of the third summary score at each assessment time ... so that I can make these average of each summary score at an initial, re-assessment, etc.
So, in short, I need the output to look like this:
Summary score 1 average at initial, summary score 1 average at second assessment, summary score 1 average at third assessment, etc.
Summary score 2 average at initial, summary score 2 average at second assessment, summary score 2 average at third assessment, etc.
Summary score 3 average at initial, summary score 3 average at second assessment, summary score 3 average at third assessment, etc.
Typicaly, I get this to this point by MANUALLY setting up the data in the following way:
Client Identifier Mr A. - date of first assessment - summary score one, etc. - date of second assessment - Summary score two, etc. - date of third assessment - summary score three, etc.
Client Identifier Mr. B - date of first assessment - summary score one, etc. - date of second assessment - Summary score two, etc. - date of third assessment - summary score three, etc.
And then I can just get the average of each of these assessments at the bottom of the sheet.
So ... does anyone know how I can automate setting up the data in this way? Or is there another way I can think about this to get what I need? The manual approach will simply not work with the size that my data is getting to.
Thanks in advance!
Dan
Bookmarks