Transforming Data

Hi, I'm new to this Forum, I hope you can help me out.

I have data in the following form: There are patients in the rows, and different Therapies in the columns. in the cells there's the Duration of the therapy in months.

Patients Therapy 1 Therapy 2 Therapy 3
p1 10 4 5
p2 6 8
p3 9 7

I'd like to transorm it in a way where I have the Duration in the rows, and the number of patients that have a certain therapy in the cells (in month 0, a certain number of patients is in therapy 1).

Time Therapy 1 Therapy 2 therapy 3
Month 0 # of patients
Month 1
Month 2

I've also attached an Excel-file with two worksheets including the examples. Is it possible to transform the data in this way?

Thanks in advance!

You can use this formula in cell B2 of the second sheet:

=COUNTIF(INDEX(Tabelle1!$B:$D,0,MATCH(B$1,Tabelle1!$B$1:$D$1,0)),SUBSTITUTE($A2,"Month ",""))

and then copy this across and down as required. See attached file, where you should see the formula translated according to your regional settings.

Hope this helps.

Pete

Thank you for your answer! But I think I didn't explain it properly. I'd like to make sort of a standardized timeline out of the months. Every patient starts therapy 1 at month 0. some stay longer in therapy 1 than others, so the number of patients in therapy one gets lower from month to month. Meanwhile, some patients already start therapy 2, and the number of patients in that line starts getting higher. Is there a way to do this with this data set?

Thanks again,
Robert

I don't understand. Perhaps you could manually mock up how you would like to see the results and re-attach the file.

Pete

Hey, I uploaded the file. The months should be a timeline in the second table. First, all three patients are in therapy 1. After one Patient goes over to Therapy 2, only 2 remain in the first therapy, and so on

Slightly different formulae in columns B, C and D this time - see attached.

Pete

Thank you both, it worked!

Robert

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete