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]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (5) |
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