you seem to be putting the same patient
into three separate age groups (<18, <19 and <20) for the three separate
dates, and that doesn't look right.
Whoops, the different ages are my error upon copy/paste. All six rows in this example should be <18, and there are only two age categories in age group anyway, either, you are <18 or 18+ dependent upon age of the encounter.
Difficult to see the gain from
duplicating the initial information down to two additional lines if it
is not already in that format.
The gain is that I have to run a census report based off certain statistics, and those repeat/unchanging columns are the certain stats. The specific report I am trying to run is a census report, and that is why each unique date/account number combo needs its own row. The database I pull this info from only contains the beginning and ending time stamps as to when the patient is in a given location of the hospital. When you wish to do a 11AM census for example, there would be two ways to do it that I could think of,
1. a snapshot in time, calculating all patients that cross or enter a location on a certain very second, say exactly 11AM, or 3PM.
2. or say a true hourly census, for example, 11AM meaning, all patients that enter or hit a location on or across any time point within 11:00:00 AM through 11:59:99.9999/100ths of a second.
As you can imagine in the first method example, what if the patient entered a location at 11:04 am and left at 11:58 am? this patient would not get counted in the 11AM "hourly census" nor the noon census.
In any event, the data base only gives me time stamp end points, beginning time patient entered a location, and ending time they left and maybe were transferred elsewhere within the hospital, but not in that location anymore. I figured out how to parse the multiple dates for the given hourly time stamps that it crosses each day, since it will be a correct hourly/daily census count now.
I would suggest that you have it coded professionally
by your IT department or an appropriately-experienced contractor.
IT is the department that is using method #1 right now, and I can see no value to undercounting patients, especially since a census is all about counting correctly. So far, I have figured out the correct answer, I just need to parse the date column into a single column rather than multiple and copy the rest of the stat info down dependent upon how many many unique dates I have given the unique patient and visit combo. In other words, if a patient stayed in a given location from 4/2/2012 at 6:45 am to 4/11/2012 at 5:45am, if I were doing an 8AM census, then that patient crossed that time stamp 10 times, 4/2-4/10/2012.
Eric Lutz
________________________________
From: "david.smart@ubs.com" <david.smart@ubs.com>
To: ExcelVBA@yahoogroups.com
Sent: Tuesday, May 1, 2012 9:06 PM
Subject: RE: [ExcelVBA] Transpose and Copy Down, slicing off columns? How do I do this other than manually?
I can't see any easy way to do this without writing VBA code.
Given that this seems to be holding patient information, and accuracy
would be critical, I would suggest that you have it coded professionally
by your IT department or an appropriately-experienced contractor.
It is also worth revisiting the problem to try to avoid doing the
transposition in the first place. Difficult to see the gain from
duplicating the initial information down to two additional lines if it
is not already in that format.
Also, double-check your example, you seem to be putting the same patient
into three separate age groups (<18, <19 and <20) for the three separate
dates, and that doesn't look right.
Regards, Dave S
________________________________
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On
Behalf Of Barry White
Sent: Wednesday, 2 May 2012 07:13
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] Transpose and Copy Down, slicing off columns?
How do I do this other than manually?
I have the following data:
Account Number Age Age Group Medical/Surgical Patient Type 8AM pass
through Before1/After0 8AM PET Before0/After1 8AM ET Date 1 Date 2 Date
3
123456789 0 <18 MED I 3 1 1 10/21/2011 10/22/2011 10/23/2011
234567890 0 <18 MED I 3 1 1 10/09/2011 10/10/2011 10/11/2011
GIVEN the number of dates in each row (date 1 -3) in this case, and
there could be up to 28 date columns---,
I need exactly that many total rows (3, one for each date), with the
account number, age, age group, med/surg, patient type, 8AM pass
through, Before1/After0 8AM PET, Before1/After0 8AM ET columnar data's
info repeated, and the date being reduced to one column, like so--
Account Number Age Age Group Medical/Surgical Patient Type 8AM pass
through Before1/After0 8AM PET Before0/After1 8AM ET Date 1
123456789 0 <18 MED I 3 1 1 10/21/2011
123456789 0 <19 MED I 3 1 1 10/22/2011
123456789 0 <20 MED I 3 1 1 10/23/2011
234567890 0 <18 MED I 3 1 1 10/09/2011
234567890 0 <19 MED I 3 1 1 10/10/2011
234567890 0 <20 MED I 3 1 1 10/11/2011
Exactly how do I do this? I cannot figure this out.
Any expertise would be greatly appreciated.
Eric Lutz
________________________________
[Non-text portions of this message have been removed]
----------
Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.
E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.
UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.
[Non-text portions of this message have been removed]
[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