Re previous thread. Matching data to reference number

[pernes]

I used the code re previous thread http://www.excelforum.com/excel-gene...ce-number.html on another group of headings, spread sheet attached. However this one is on therapies given to a patient in a month, so one patient might have had several different treatments, but only one is showing.

How can I get

Month of treatment against each reference number
what treatment/s a patient (reference number) has had and in what month and total number of treatments over the year
total number of treatments given per month ie counselling, aromatherapy etc

Thanks in advance.

(therapies.xlsb

Raised as a new thread as I couldn't upload new spreadsheet in previous thread)

[ThirtyTwo]

Hi

Here are the steps I took to get this new file to have same layout as the Diagnosis file


Create list of each therapies
Select Sheet1 C1 : N1

Select Sheet2 A2
Paste Special, Values, Transpose


Create column for Month
Insert new column before Column O
Title this new column as Month

Put Month name on each row
O3 formula is: =IF(Q3="","",IF(A3<>"",A3,O2))
(copy and paste down to row 540)

Put all data onto a new sheet

Select all data from Sheet1 O1 : Q540
Copy
Select new sheet, Sheet3
Select cell A1
Paste Special, Values

Put Drop-downs onto treatment column of new sheet

Select Entire column C
Data -> Data Validation -> Settings -> Allow: List, Source: =Sheet2!$A$2:$A$13

Remove drop down from header
Select C1:C2
Data -> Data Validation -> Settings -> Allow: Any value


Summarise the Data

Select all data Sheet3!$A$1:$C$540 (or Sheet3!$A:$C , with no row numbers it will include new data that is added)
Insert -> PivotTable -> Placed -> Existing Worksheet: Sheet3!$G$1

Choose fields to add to report: Select All (Month, Reference Number, treatment)
Row Labels: Month, treatment
Drag Reference Number to Values (it then changes to Count of Reference Number)

[pernes]

Hi, thank you for the response. Sorry for not replying earlier but other commitments have meant I have been unable to have a chance to get back to the spread sheet.

As you can probably tell I am a complete novice on this and probably trying to achieve too much with my limited knowledge!, so I am very grateful for your help.

[Fotis1991]

Raised as a new thread as I couldn't upload new spreadsheet in previous thread)

Why? You couldn't do that in previous thread and you can on this one??. So:

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.