Nice people thread part 3- Nice as pie

misskool

GDB2222 wrote: »

You are describing the data, not what you need to do to it. Are you trying to update it, or extract information from the four spreadsheets? Are you doing this for one patient at a time or in bulk?

oops, sorry.

I need to reconcile where each patient sample is with each patient. So if I'm looking for patient A001, I need to find where each of the samples are across various timepoints. I need to know which patient are missing timepoints, which patients are missing histo. then when I finish the study, I need to reconcile patient information with the results and do some statistical analysis to see if what I find can predict something.

It would be nice to find blocks of patients but one at a time is good enough.

I doubt we will be doing any modelling or anything exciting.

GDB2222

LydiaJ wrote: »

Thanks, GDB, but that makes sloping lines. I want flat ones and vertical steps on the dates where the changes occur.

I've now realised that probably the easiest way to do it is to incorporate these numbers into a larger spreadsheet with lots more data, and stuff happening almost every day. The money balance I want to graph will then have lots and lots of points to plot where it's the same, and then the steps will be nearly vertical, which is good enough.

You can do what you want, but you need to adjust the data, like this:

01/08/2011 20000
20/08/2011 20000
20/08/2011 22000
08/09/2011 22000
08/09/2011 24000
27/09/2011 24000
27/09/2011 26000
02/10/2011 26000
02/10/2011 28000
04/12/2011 28000
04/12/2011 30000
23/06/2012 30000
23/06/2012 32000
30/07/2012 32000


(That's the same data as before, but carefully duplicated)

Then highlight it, click the chart wizard, and choose XY Scatter.

LydiaJ

Yes, that's exactly what my work-around does, except that it generates the duplicated list from the wider data with a few user-defined functions.

How encouraging to find somebody else finding the same solution as me. Thanks.

GDB2222

misskool wrote: »

oops, sorry.

I need to reconcile where each patient sample is with each patient. So if I'm looking for patient A001, I need to find where each of the samples are across various timepoints. I need to know which patient are missing timepoints, which patients are missing histo. then when I finish the study, I need to reconcile patient information with the results and do some statistical analysis to see if what I find can predict something.

It would be nice to find blocks of patients but one at a time is good enough.

I doubt we will be doing any modelling or anything exciting.

Is the data in the spreadsheets complete, or is it still being added to?

misskool

Still being added to and new spreadsheets will appear as the study progresses.

GDB2222

In that case, i think you'll want to adapt the methodology on this page:

http://www.excelforum.com/excel-worksheet-functions/599751-lookup-multiple-instances.html

GDB2222

I'll just add that it's not trivial to do this, but I think you'll have less grief if you keep it all within Excel than if you try to do this within Access. That's assuming that the other people involved in the project are going to be happier updating Excel spreadsheets than having to learn to use Access.

I've assumed that patients will keep having samples taken from them over an indefinite period of time, and they'll just get added to the bottom of the spreadsheet.

If this is going to be a really big project, you would of course want to expend the time to set up a proper database application to store the data, then train people to update it correctly, including adding new tables of data. But then you would be in the realms of an IT project, rather than a bit of research.

misskool

Samples will be taken over 5 years if the patients live that long or until mortality or relapse.
I'll investigate the excel option since it *should* be easier than learning a whole new piece of software. I don't mind updating it and I'm sure I could train people once it's set up.

GDB2222

Roughly how many patients are you talking about, and how often will samples be taken?

misskool

200+ for this study, others will be more others will be less. I have to organise approx 6 studies...

each patient will have initial B and S samples, then pre and post surgery B and S samples, then B and S samples every 6 months until 5 years, mortality or relapse. I have different numbers of B and S samples and I need to know how many of each samples are left and where each and every one of them is stored.

edit: thank you!!