Help with reporting on data sheet.

[Satele]

Hi all,

I'm really struggling with this time-wise, though I know it can be done. I have two workbooks, one with my data, one with a report template. For simplicity, I've put some sample data from the data workbook in a separate sheet of the report workbook (attached if this is too confusing to follow).

My data worksheet can not be altered, and I need to use formulas on the report sheet to pull back data from the data sheet in an automated way.

In my data sheet, there are 10 columns of data:
A = UID
B = RecordType
C = HCode
D = AdmittedDate
E = Forename
F = Surname
G = DOB
H = Sex
I = STDate
R = RDate

An example of my reporting sheet is in the table below. What I need this sheet do might be fairly straight forward. If not, here's an explanation. Under 'Jul-12', I need the sheet to pull back the number of 'Assessments' and 'Reviews' completed in that month (reviews are in the form of 'transfers' and 'discharges' - 'Reviews' would just be a SUM of these two).

So far, this is fairly simple and could be achieved with SUMPRODUCT OR VLOOKUP. However, the difficulty is when I want to pull back the reviews, these need to be not just any review completed in that month but only reviews for the same patient who had an assessment, as determined by the 'UID' column in the data sheet.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Hospital Report[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reporting Quarer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital Name[/TD]
[TD]Hospital Code[/TD]
[TD]Record Type[/TD]
[TD]Jul-12[/TD]
[TD]Aug-12[/TD]
[TD]Sep-12[/TD]
[/TR]
[TR]
[TD]St George[/TD]
[TD]STGE1[/TD]
[TD]Assessments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Reviews[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Transfers[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Discharges[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


An example of my data sheet:

[TABLE="width: 500"]
<tbody>[TR]
[TD]UID[/TD]
[TD]RecordType[/TD]
[TD]HCode[/TD]
[TD]AdmittedDate[/TD]
[TD]Forename[/TD]
[TD]Surname[/TD]
[TD]DOB[/TD]
[TD]Sex[/TD]
[TD]STDate[/TD]
[TD]RDate[/TD]
[/TR]
[TR]
[TD]89654[/TD]
[TD]STAssess[/TD]
[TD]STGE1[/TD]
[TD]01/01/2012[/TD]
[TD]Andrew[/TD]
[TD]Macbeth[/TD]
[TD]13/07/78[/TD]
[TD]m[/TD]
[TD]08/07/12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]87676[/TD]
[TD]Transfer[/TD]
[TD]STGE1[/TD]
[TD]01/01/2012[/TD]
[TD]Mark[/TD]
[TD]Jones[/TD]
[TD]18/01/67[/TD]
[TD]m[/TD]
[TD][/TD]
[TD]08/08/12[/TD]
[/TR]
[TR]
[TD]89654[/TD]
[TD]Transfer[/TD]
[TD]STGE1[/TD]
[TD]01/01/2012[/TD]
[TD]Andrew[/TD]
[TD]Macbeth[/TD]
[TD]13/07/78[/TD]
[TD]m[/TD]
[TD][/TD]
[TD]12/08/12[/TD]
[/TR]
[TR]
[TD]87637[/TD]
[TD]Transfer[/TD]
[TD]STGE1[/TD]
[TD]01/01/2012[/TD]
[TD]Steve[/TD]
[TD]Jones[/TD]
[TD]09/05/86[/TD]
[TD]m[/TD]
[TD][/TD]
[TD]17/08/12[/TD]
[/TR]
[TR]
[TD]87676[/TD]
[TD]STAssess[/TD]
[TD]STGE1[/TD]
[TD]01/01/2012[/TD]
[TD]Mark[/TD]
[TD]Jones[/TD]
[TD][/TD]
[TD][/TD]
[TD]02/07/12[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So, in my report table above, the hospital code 'STGE1' should pull back the number 2 next to 'Assessments' under July. The formula should look for any records in the data sheet with a valid July 2012 date in the 'STDate' column. This part, I can do easily enough.

However, next to 'Transfers' in my report sheet, I only want to pull back the number of 'Transfer' records in the 'data' sheet with a corresponding 'Assessment' under 'RecordType'. i.e. the one with the UID 87637 Should not be counted. I also need the formula to only pull records that occur in the month as typed in the month for that particular column of the report sheet (cell D4: July 2012). All assessment dates are in the column 'STDate' and all review dates are in the column 'RDate'.

Can someone please help with this? I'm banging my head off a wall and need a fairly quick resolution!

Thank you,
S.

 

[Satele]

I'm not following how I would do that whilst using pivot tables.

 

[oldbrewer]

you can convert csv files to excel - I used to do it before I retired - somebody on here will soon remind us....

 

[oldbrewer]

[h=2]Import a text file by connecting to it[/h]You can import data from a text file into an existing worksheet as an external data range.

• Click the cell where you want to put the data from the text file.
• On the Data tab, in the Get External Data group, click From Text.

• On a computer that is running Windows Vista

• In the Address bar, locate and double-click the text file that you want to import.

On a computer that is running Microsoft Windows XP

• In the Look in list, locate and double-click the text file that you want to import.

Follow the instructions in the Text Import Wizard. Click Help

for more information about using the Text Import Wizard, or see Text Import Wizard. When you are done with the steps in the wizard, click Finish to complete the import operation.

• In the Import Data dialog box, do the following:
  • Optionally, click Properties to set refresh, formatting, and layout options for the imported data.
  • Under Where do you want to put the data?, do one of the following:
    • To return the data to the location that you selected, click Existing worksheet.
    • To return the data to the upper-left corner of a new worksheet, click New worksheet.
• Click OK. thanks to microsoft site

Excel puts the external data range in the location that you specify.
If Excel does not convert a column of data to the format that you want, you can convert the data after you import it. For more information, see the following Help topics: