Comparing multiple rows of data

[stewpot]

I have hundreds of rows of patient data and within that data I need to look
at each individual patients data.
For every patient I have several rows of data, each row contains the same
fields but they relate to different events in their hospital stay which are
numbered 1,2,3,etc. I am trying to compare the latest event with the previous
event (e.g. event 3 with event 2) and show whether their test results (which
is a number) increase or decrease and if for instance it increases and is now
over 30, count it as a yes and if it goes down count it as a no. The macro
will need to identify the most recent event and then compare this to the
previous event, this will be different for each patient record. Can you help?

 

[Joel]

Th esolution to this problem depends on where you place the results and how
you want to review the results. Some people may recommend for you not to use
a macro and use sumproduct, but I think this is not the best solution.

My recommend datations is to copy the data to another worksheet and to sort
by patient names and dates. Then remove any patients who where in the
hospital only once and to keep only the last two visits to the hospital for
each patient. I can help write the macro if this is what you want.

It would help if you can post a sample of the patient data. sort the
patient by name and dates so we can see the columns top compare. then change
the patient names and other vital info. You only need to post a few examples
of the data along with the columns and worksheet name where the data is
located.

 

[Tom Ogilvy]

Stewpot,

Select all your data, then do data=>Filter=>Autofilter

then in the column for patient ID (or name or other unique identifier),
click the dropdown arrow and select that patient.

This will hide all but the records for that patient.

In the dropdown, All will remove that filter condition. Data=>Filter=>Show
All will remove all filter conditions. Data=>Filter=Autofilter will remove
the filter.

http://www.rondebruin.nl/0307commands.htm will give you Excel 2007
equivalencies if you need that.

 

[stewpot]

Hi Joel
The data I have comes from an Oracle 10g db, the user in the hospital opens
up the excel workbook which contains 3 worksheets. The first options
worksheet asks the user to select the start and end dates for the report and
three other variables ( all run by macros behind select buttons.) The other
two worksheets are the results worksheet which is a preformatted report,
showing only the data relating to the variables chosen above and the last
sheet is the actual data retrieved from Oracle with all the code, defined
names etc used on the results sheet.
What else do you need in able to help me??

 

[stewpot]

Joel
I forgot to say that the data already comes ordered by patient and date,
when I run the query from Oracle

 

[Joel]

We are getting closer to getting all the info needed to write the macro or
formula.


1) Are you going to run the macro on the preformatted report or the last
sheet with all the data?

2) What is the name of the workshheet.

You may be able to use a simple formula. If the event number is in column A
and the data is in column B

Put in a new column in Row 2
=if(A2<>1,if(B2>B1,if(B3>30,"Increase over 30","Increase under
30"),"decrease"),"")

or
=if(A2<>1,if(B2>B1,"Increase","Yecrease"),"")

 

[stewpot]

Hi Joel
Apologies I got caught up with other things!
I have a mainly used the macros on the front sheet where the user defines
the time period etc they want. On the Data sheet I have defined all the names
reuqired and then refer to these using formulae on the results page.
The Workbook is called Rehab_test.xls and the three worksheets are Options,
Analysis and Data.
Along with the patients hospital number, I will need to use the sequence
number (i.e 1,2,3 etc) to compare the latest two records OR none if they only
have 1 sequence number and then the third column is the column with the BMI
result which is a number and if that has decreased I need to count 1 and if
it has increased count 0. There will be hundreds of rows of patient data .
Many thanks for your help

 

[Joel]

You haven't provided enough info to completely describe the problem. Can you
send me a worksheet with a couple of patients (John Smith and Mary Jones).

joel dot warburg at itt dot com