Dear Eric Lutz and/or Barry White
This is similar to something I am doing in a similar context. The code below may help you although I do agree with David that you may need professional help. The code only de-duplicates overlapping minutes within consecutive rows for the same department (which looks to me like what you want) but with a bit of ingenuity you will be able to adapt it to do something else as soon as you understand the principle.
The code assumes you have three columns, a department name then two correctly formatted date/time columns. If you can't get Business Objects to make a report like this then you will have reformat your report in Excel. I was a BO consultant for many years and I can tell you first that it will be very easy for your BO administrator to format the report like this for you. I can also tell you that BO will not do the date de-duplication.
In the code I am converting each individual minute of the patient's stay in the department into an integer. I then add each of these integers to a collection as keyed items. As the collection will not allow you to add an item that already exists. the collection Count property becomes the de-overlapped minutes' count.
To use this you will have to expand the code in the second Debug.Print line to consolidate the rows into a new worksheet. I am not proud of the code that tests for departments change.
If you do need professional help I am sure there are people on this forum who can recommend somebody.
Sub PatientIntradayLook()
Dim nRow As Long, nMinuteIndex As Long
Dim clxMinutes As New Collection
Dim dateIn As Date, dateOut As Date, nMinuteIn As Long, nMinuteOut As Long
Dim dateEarliest As Date, dateLatest As Date
For nRow = 2 To Range("a1").CurrentRegion.Rows.Count ' row 1 is title
dateIn = Cells(nRow, "b")
dateOut = Cells(nRow, "c")
nMinuteIn = CLng(CDbl(dateIn) * 1440) ' minutes per day
nMinuteOut = CLng(CDbl(dateOut) * 1440)
If Not Cells(nRow, "a") = Cells(nRow - 1, "a") Then
With clxMinutes
While .Count > 0
.Remove 1
Wend
End With
dateEarliest = dateIn
dateLatest = dateOut
End If
If dateIn < dateEarliest Then dateEarliest = dateIn
If dateOut > dateLatest Then dateLatest = dateOut
For nMinuteIndex = nMinuteIn To nMinuteOut
On Error Resume Next: clxMinutes.Add nMinuteIndex, "key" & nMinuteIndex: On Error GoTo 0
Next nMinuteIndex
Debug.Print Cells(nRow, "a"), nMinuteOut - nMinuteIn, dateEarliest, dateLatest
If Not Cells(nRow, "a") = Cells(nRow + 1, "a") Then
Debug.Print Cells(nRow, "a"), clxMinutes.Count - 1, dateEarliest, dateLatest
Debug.Print ' make new row in new sheet here
End If
Next nRow
End Sub
Regards
Derek Turner
MS Office Automation and Business Objects Consultant.
Watford, Hertfordshire, England
+++
>________________________________
> From: Barry White imtigerwords@yahoo.com>
>To: "ExcelVBA@yahoogroups.com" ExcelVBA@yahoogroups.com>
>Sent: Thursday, 14 February 2013, 0:50
>Subject: Re: [ExcelVBA] Patient Intraday Look Table and its wonderful Dysfunctions
>
>
>
>1. These items are created in a reporting tool called business objects, which is hooked up to a "database" called healthquest. I use the word database loosely since data is somehow input like this.
>
>2. Okay maybe not so much gaps in this example (there are in other examples), but at the very least overlapping times, meaning duplication in time, when the ultimate goal is a FULL UNIQUE PICTURE of how a patient passes through hospital on an intraday basis for their whole stay.
>
>3. I believe times are entered manually.
>
>4. Yes 292 belongs at the end of the previous line, I cut it wrong by accident.
>
>5. The first line differs with the semicolons because I put those in to show you where the breaks in the data are, they are NOT part of the original data, I did it for illustration purposes.
>
>6. I will be willing to eliminate a nursing stations (one or the the other) if it contains overlapping minutes, in the situation where there are two different nursing stations/departments.
>
>7. I was hoping some VBA would possibly compare one line to another while holding the first line in a buffer in order to compare the next in the list, but I did not know exactly how to start it.
>
>Even though you feel I would need something more robust than Excel or Access, I wanted to attempt something, at least on a small scale, with a few patients in a list rather than a whole file.
>
>8. I couldn't agree more that the input is ridiculous. For some reason they don't understand the value of knowing the exact total minutes of time the patient stayed in the hospital, distinct and non-overlapping minutes, no less.
>
>9. What I am aiming to produce from all this, is a unique and distinct full picture of time in the hospital for each patient stay by each nursing department, in chronological order, from the first day to the last day at the hospital, NOT merely a total minute amount. I will need to count the number of total unique nursing department switches (starting in the ER, moving to cancer floor, and moving back to ER for some reason IS 3 distinct nursing departments, but having three rows of data all labeled the same, is only 1 distinct nursing department). I will also need to count time within each distinct nursing dept.
>
>Eric Lutz
>
>________________________________
>From: David Smart smartware.consulting@gmail.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Wednesday, February 13, 2013 6:37 PM
>Subject: Re: [ExcelVBA] Patient Intraday Look Table and its wonderful Dysfunctions
>
>
>
>
>You haven't indicated where these items are created. Your example shows no
>gaps in the times that I can see. If the times are entered manually, then
>gaps are inevitable, as are minor overlaps sue only to mis-entering of
>times.
>
>You also have a 292 at the start of a line, where it should presumably be at
>the end of the previous line. If this is in the actual data, then you have
>a checking function which needs to be undertaken first.
>
>Additionally, the first line differs from the rest, in that it has semicolon
>separators. Again, the data will need reformatting before further analysis.
>
>Compression of unnecessary multiple lines will not be all that simple. This
>is because there are also duplications of the time periods, and these don't
>line up. Worse, we have duplications that aren't even for the same
>department (e.g. the first two lines). It is, of course, fairly
>straightforward to separate out the different time lines and ensure that
>they are cut down into single items; and it's also easy enough to eliminate
>redundant time lines for the same department. But you are going to need to
>handle the situation where overlapping time lines refer to different
>departments (as in the first two lines), and even when time lines go nowhere
>(as in the first line).
>
>This is really a job for a professional programmer and will involve quite a
>bit of data manipulation.
>
>> Imagine a plethora of 200,000 patients in a years time,
>> all ridiculously logged just like this.
>
>If it's this many, then you want something more robust than Excel or Access.
>It needs to be done in a full-scale programming language, and probably using
>a full-scale database engine.
>
>But, the input really is ridiculous. Why isn't it being fixed?
>
>The other question is, what are you aiming to produce from all this? You
>haven't said. But, if it's just to arrive at the patient's 25.12 days, then
>all you need to do is look for the minimum and maximum date/time items for
>the patient, and subtract.
>
>Regards, Dave S
>
>----- Original Message -----
>From: "Barry White" mailto:imtigerwords%40yahoo.com>
>To: mailto:ExcelVBA%40yahoogroups.com>
>Sent: Thursday, February 14, 2013 6:26 AM
>Subject: [ExcelVBA] Patient Intraday Look Table and its wonderful
>Dysfunctions
>
>Hello all,
>
>Looking for a little help as to how to combat the three dysfunctions in this
>table (don't roll your eyes at me, I didn't make this mess, I am just trying
>to clean it up)
>
>The three main dysfunctions in this Patient Intraday Table View are,
>
>1. I need to nix the duplicate or overlapping minutes. This table should
>show unduplicated time stamps and nursing floors where the patient is
>staying in a bed. As the patient gets moved around to different parts of the
>hospital, you should see no gaps in the time and date stamps, when the
>patient is entering or leaving from one nursing floor locale to another.
>
>2. This whole file needs to be compressed to its most efficient list. Many
>times I see the same nursing floor repeated back to back to back, where one
>row of data will do, ultimately displaying a single entry time and date, and
>a single exit time and date
>
>3. Given this list of inefficiency and duplication, sometimes there is more
>than one viable path that can be taken to arrive and the correct and
>unduplicated minutes of total time stayed within the hospital walls.
>
>A fine example is below. This made up patient ultimately stayed in the
>hospital for 25.12 days, from 12/16/2011 @ 3:21PM to 1/7/2012 @ 1:28PM, or
>36,177 total minutes. As you can see from this snippet below there was
>39,420 duplicate minutes mixed in, that need to be weeded out.
>
>I need to turn this gobbleygook entirety,
>
>Nursing Wing; Start Date; Start Time; End Date; End Time; Minutes between
>Times;
>
>Emergency Room; 12/16/2011; 3:21 PM; 12/16/2011; 3:33 PM; 12;
>Holding Area 12/16/2011 3:21 PM 12/16/2011 6:00 PM 159
>Holding Area 12/16/2011 6:00 PM 12/16/2011 6:51 PM 51
>5th Floor South 12/16/2011 6:51 PM 12/16/2011 11:43 PM
>292 Cancer Floor North 12/16/2011 11:43 PM 12/18/2011 1:14 PM 2,251
>Cancer Floor North 12/18/2011 1:14 PM 12/23/2011 5:52 AM 6,758
>Intensive Care East 12/23/2011 5:52 AM 12/23/2011 6:08 AM 16
>Intensive Care East 12/23/2011 6:08 AM 12/23/2011 6:39 AM 31
>Intensive Care East 12/23/2011 6:39 AM 12/23/2011 6:40 AM 1
>Intensive Care East 12/23/2011 6:39 AM 12/24/2011 12:00 PM 1,761
>Intensive Care East 12/23/2011 6:39 AM 12/25/2011 1:56 AM 2,597
>Intensive Care East 12/23/2011 6:40 AM 12/24/2011 1:56 AM 1,156
>Intensive Care East 12/24/2011 1:56 AM 12/25/2011 1:56 AM 1,440
>Intensive Care East 12/24/2011 12:00 PM 12/25/2011 1:56 AM 836
>Cancer Floor North 12/25/2011 1:56 AM 12/29/2011 12:00 PM 12,728
>Cancer Floor North 12/25/2011 1:56 AM 01/05/2012 11:03 PM 17,107
>Cancer Floor North 12/29/2011 12:00 PM 01/05/2012 11:03 PM 21,486
>General Med 3rd Floor 01/05/2012 11:03 PM 01/07/2012 1:28 PM 6,915
>
>Into this efficient path,
>
>Nursing Wing; Start Date; Start Time; End Date; End Time; Minutes between
>Times;
>
>Holding Area; 12/16/2011; 3:21 PM; 12/16/2011; 6:51 PM; 210;
>5th Floor South 12/16/2011 6:51 PM 12/16/2011 11:43 PM 292
>Cancer Floor North 12/16/2011 11:43 PM 12/23/2011 5:52 AM 9009
>Intensive Care East 12/23/2011 5:52 AM 12/25/2011 1:56 AM 2644
>Cancer Floor North 12/25/2011 1:56 AM 01/05/2012 11:03 PM 17,107
>General Med 3rd Floor 01/05/2012 11:03 PM 01/07/2012 1:28 PM 6,915
>
>The highlighted yellow portion from the first piece contains the
>non-duplicate trip through the hospital as it is displayed in the second
>piece concisely.
>
>Thank you for any help that can be provided, as this is making my head spin.
>Imagine a plethora of 200,000 patients in a years time, all ridiculously
>logged just like this.
>
>I would gladly take either Excel or Access help on this. Any suggestion
>would do at this point.
>
>Sincerely,
>
>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/
>
>----------------------------------Yahoo! Groups Links
>
>-----
>No virus found in this message.
>Checked by AVG - www.avg.com
>Version: 10.0.1430 / Virus Database: 2639/5600 - Release Date: 02/12/13
>
>[Non-text portions of this message have been removed]
>
>
>
>
>
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (8) |
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