Monthly hospital admissions data

Hi All,

Looking for some wisdom from experienced Excel users.

I've got monthly hospital admission data that I don't know what to do with.

Every month I get new data describing hospital bed utilization (number of admissions, discharges, deaths, etc), and need it 'organized'.

This is likely a basic question, but I'm not sure how to start the process. I can use a pivot table to select specific months or units later, but how do I organize all this at the start?

Many thanks for suggestions.

Terry

I've attached an image to show what form the data is in. Note that its very clean and 'importable' to somewhere.

I should have put in a few extra details:

BD DAYS = bed days
ADM = admissions
DIS = discharges
EXP = expiries (deaths)
PD = patient days
etc...

What I want to be able to do is import text files like these *somewhere* and look at this data over time. How many admissions did 4NA have this year vs. in 2005?

Also, the text files change somewhat from month to month. The units stay the same (5NA, 4NA, 5SB, etc...), but they change in the text file from month to month. So they would need to be imported not by line, but by unit I assume.

Anyway, suggestions from anyone with ideas or thoughts on this would really be appreciated.

Hi,

It's reasonably clean but not clean enough for using in a Pivot Table and Pivot Chart. One you've imported it to Excel with the Data Get External Data, you'll need to delete the 4 rows between the column headers starting with 'Location', and add a row above the column headers to separate the table from the first row which contains the Period to Data & Year to Date titles.

Then you'll need to sort the data so that you remove the blank rows which occur every so often. nce you've done that you can click anywhere in the table and start your Pivot Table analysis.

If you're doing this regularly you'll probably want to create a macro to do that for you.

HTH

Thanks Richard for some very good advice.

I think the thing I'm having most trouble with is: how do I associate the month with all the values? Next month I'll import a Dec 08 file, and then a Jan 08 file.... how do I associate all those different months and values with specific units?

Thanks again!

Hi,

So the data is not as clean as you originally suggested!

Although you say the units stay the same, they clearly don't exists as a discrete field of data, they seem to be contained within the Location and BD strings. So one of the first things to do is add an additional column and use a formula to extract the unit code from the string.

Then you say that you want to compare this data over time, but apart from the file header which says AUG 08 there is no time recorded against each row of data. Hence you are going to need another column which will contain the same date for each of the rows. I suggest you add the month end date as a proper date number. i.e. in this case enter 31/08/2008 in each row.

Then, and only then can you start your pivot table analysis. You will of course have to import each months data to the same sheet so that gradually you build up a large table of data covering many months.

HTH

Hi Richard,

Thanks again and again. You were right - the data isn't that 'clean'. Perhaps I don't quite know what clean is! =)

Excellent suggestion with adding the date column.

The next step would be to import each month somehow. Lets say I add a date column to the text file, how would I import the file so that July's 4NA data goes in with August's 4NA data? Do I really need a database?

Many thanks for your help with this - I'm glad there are knowledgeable people out there willing to help those just starting out.

Thanks -- Terry

Hi,

Yes, that's exactly what you want - a database. Each month you import the next months data you simply add it to the foot of all the data you've loaded in previous months. What I generally do in cases like this is keep the table of data accumulated over the months in one sheet. Then each month first delete all last months working data, then import the current month's data. Tidy up the data by deleting the rows you don't want and adding the dates you do want, and finally when it's in a clean state move it over to the database sheet and refresh your pivot table.

HTH

Hi richard,

I think you're exactly right. next I need to figure out how to get these records into a database. I think I'm having a conceptual issue: how do you get this month's 4NA to go in next to the info for 4NA next month?

does 4NA and 4NB and 5NA, etc....get set as the primary keys?

Thanks again for all your help.

Hi,

Once you've got your database sheet sorted out with the first months data, and the two additional columns for Date and Unit Ref as the first two columns, just use this as the master database.

If you're doing this manually, each month just open up the new file, sort out the records deleting rows and adding new columns, then just copy and paste it into your master database underneath the last record from the previous month. Continue to do this each month. Obviously it would make sense to have a macro do all of this for you automatically, so once you're comfortable with what you're doing manually, try recording the process you go through with the macro recorder.

You'll almost certainly need to edit the macro once you've recorded it, but it will give you a good start.

HTH