Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing multiple rows of data
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing multiple rows of data
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. "stewpot" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing multiple rows of data
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. -- Regards, Tom Ogilvy "stewpot" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing multiple rows of data
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?? "Joel" wrote: 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. "stewpot" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing multiple rows of data
Joel
I forgot to say that the data already comes ordered by patient and date, when I run the query from Oracle "Joel" wrote: 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. "stewpot" wrote: 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing multiple rows of data
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(B2B1,if(B330,"Increase over 30","Increase under 30"),"decrease"),"") or =if(A2<1,if(B2B1,"Increase","Yecrease"),"") "stewpot" wrote: 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?? "Joel" wrote: 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. "stewpot" wrote: 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing multiple rows of data
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" wrote: 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(B2B1,if(B330,"Increase over 30","Increase under 30"),"decrease"),"") or =if(A2<1,if(B2B1,"Increase","Yecrease"),"") "stewpot" wrote: 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?? "Joel" wrote: 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. "stewpot" wrote: 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing multiple rows of data
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 "stewpot" wrote: 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" wrote: 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(B2B1,if(B330,"Increase over 30","Increase under 30"),"decrease"),"") or =if(A2<1,if(B2B1,"Increase","Yecrease"),"") "stewpot" wrote: 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?? "Joel" wrote: 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. "stewpot" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing data in different rows | Excel Worksheet Functions | |||
Comparing several rows of data for 1 record | Excel Programming | |||
Comparing Absolute Column on multiple rows | Excel Programming | |||
Comparing rows of data in two worksheets | Excel Programming | |||
Comparing rows of data in two worksheets | Excel Programming |