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" imtigerwords@yahoo.com>
To: ExcelVBA@yahoogroups.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
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (6) |
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