Hello all,
I am viewing multiple patient's medical data.
The very first visit by that patient over a given timeframe is designated with "999999"
For each subsequent visit by that same patient I wish to calculate a running total of days since the discharge date of the initial visit. The date of discharge is the second column of dates in the set below. The first column of dates are the admit dates.
A new patient's initial visit begins, when you run into another set of "999999"
(I am not particular as to what value I give the initial visit, it could be 999999 or any value as long as its consistent).
So in row 2, with the admit date being 2/23/2010 and the discharge of 2/27/2010, the days since the initial visit is 5, as seen in column 3, and it is calculated as
2/23/2010 minus 2/18/2010
The third visit by this same patient is an admit date of 3/6/2010 and discharge of 3/10/2010
So you could either take 3/6/2010 minus 2/18/2010 for the result of 16, OR you could do it the way I have done it below, which is to take 3/6/2010 minus 2/23/2010 = 11 (subtracting admit dates), and add the 11 from the 3rd row of the fourth column to the 5 from the second row in the third column.
That 5 + 11 = 16, the 16 will need to be added to the 56, yielding 72 as a running total. The 72 will need to be added to the 177 to equal 249, the 249 in turn added to the 45 to ultimately give 294 days as the total days from the first visits day of discharge.
The problem comes in because each patient can have a unique and totally different number of visits, within a given time frame, so you never know when the "chain" will end.
Sometimes it ends with two visits only, sometimes 10, or 3, or 5, or 7, etc.
02/16/2010
02/18/2010
999999
999999
02/23/2010
02/27/2010
5
7
03/06/2010
03/10/2010
7
11
05/01/2010
05/03/2010
52
56
10/25/2010
10/27/2010
175
177
12/09/2010
12/16/2010
43
45
12/28/2009
01/01/2010
999999
999999
01/28/2010
02/09/2010
27
31
06/05/2010
06/10/2010
116
128
08/16/2010
08/20/2010
67
72
10/04/2010
10/07/2010
45
49
02/21/2010
02/23/2010
999999
999999
01/21/2011
01/29/2011
332
334
06/30/2010
07/02/2010
999999
999999
01/01/2011
01/04/2011
183
185
Eric Lutz
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar