I am trying to find a formula that would count an item once, even though, it possibly could appear in the data multiple times. These are babies who stayed in our NICU overnight. We populate this spreadsheet daily, with who was in there at midnight. So, if a baby stays in there for 10 days, they will be on this spreadsheet 10 times. The data element that I'm trying to get at is the # of admits (column Q). I did a simple formula that said if the Date of service (column J) was the same as the babies admit date (column C), to put a 1. There are times though that the baby will not go straight to the NICU. In that case, I am not ever counting that baby as an admit. I need a way to identify those patients that have not been counted yet in column Q, but only count them on their first day in the NICU.

Attached Files

SCN Log 2013.xlsx (360.7 KB, 17 views)

Hi and welcome
Could you please explain some more? How can one determine when a baby does not go straight to the NICU ( I have no idea what this is) on your spreadsheet?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

If the baby's admit date is less than their first occurance date, in column J, then they were possibly in with the mother when they were first born, not needing to be in the NICU. Does this make sense? An example of this is Pt Nbr (column B) is 2452590. As you can see, their first day in the NICU was 1/11/13, but baby was born on 1/9/13. So, in this instance, I would want to count the baby as an admit on the 1/11/13 day only, even though the baby stayed in the NICU for 48 days.

Carol, is this just a matter of counting how many different patient numbers there are in a given range of dates? Or do you need to count a patient twice if it was admitted, then moved elsewhere, then admitted again (because of a relapse, say)?

Each person can only be counted once, regardless if they relapsed or not.

So forget looking at columns C, J and Q; all your formula needs is to look at column B and report how many different patient numbers are there, no matter how many times they appear. Will that work, or am I missing something? In the worksheet you posted, I see 244 different patient numbers. If you have a formula that tells you that for any range of rows, will that satisfy your requirement?

I thought I had posted a reply yesterday, but I don't see it here, so I will repost, just in case.

No, I need more than just a simple formula that counts up the # of different patient account #'s. The # of admits is one of the main key indicators that hospitals have to report, so it is important that we count the baby in the correct month, and only once, for the whole stay. Here are a few scenarios that will hopefully help explain better.

Scenario 1:
Baby born 12/29/13 - went into NICU right away
Admit will be counted for 12/29/13
Baby still in NICU through 1/20/14.
In January I would not count an admit on the baby, since they STARTED in the NICU in December.

Scenario 2:
Baby admitted 12/29/13 - to a regular room
Baby became ill and had to go into NICU on 1/2/14
Baby in NICU through 1/10/14.
I would need to count this baby as an admit on 1/2/14, since that is when they came into the NICU. Normal (non-sick) babies do not get counted, so since baby was in a normal room with the mother, it wasn't counted in December. We would only count the baby once it got admitted into the NICU.

Does this help?

I think it helps, but let me rephrase to make sure. No, first let me ask something I should have asked at first: Would I be right in the following?

• SCN: "Special-Care Nursery"
• NICU: "Neonatal Intensive-Care Unit", or perhaps just "Natal...".
• What is the relationship between the SCN and the NICU? Are they the same thing? I ask because you want to know about admissions to the NICU, but this worksheet seems to have data only on the SCN; if they're not the same thing then I'm missing a datum.
• The contents of Sheet1 are a nightly census of patients in the SCN, each patient taking one row. The date of each census is in column J; the 01-01 census is in rows 4-10, the next night's census is in rows 11-17, and so on. Therefore a patient who was in the SCN on several consecutive nights will appear that many times in the worksheet.

Now let's return to my mistake of yesterday. I've just reread your initial explanation, and if from now on I may treat "SCN" and "NICU" as synonymous, then I think I understand it better. Let's test my genius with a few questions design to humiliate me if I'm still not getting it:

1. Patient 2437187 appears on four rows, ie the censi of 01-01 through -04. How many times do you want this patient to be counted for this requirement? My answer: 0. The patient was admitted the previous month, and therefore is not to be counted at all in admission count for 2013.
2. Patient 2605119 was admitted 2013-06-28 and appears in every census but one from there to 2013-09-20; 84 appearances, missing only 08-07. That out-and-in is apparently not to be counted as a new admission; the Admit date is listed as 06-28 throughout. Should be counted as 1.
3. I couldn't find an example of this one, but let's pretend: Patient 9999999 was admitted, according to column C, on 2013-02-13 and appeared in the next 13 censi, the last one being 02-25. The same patient number appears again with a new admission date two weeks later, 03-11 through 03-17, an 8-night stay. Since there are two admission dates for this patient, it should be counted as 2 for this exercise.

If all of this is correct, then what you want, I think, is a process like— No, wait. I can think of a couple of different ways to do that, but let's not get into that until I know whether I'm on the right track. Does it sound like I understand the requirement, yet, or am I still missing something?

You are definitely on the right track. I wanted to clarify something on the 3rd scenario - if they were admitted 2 weeks later, they would be assigned a different account #.

SCN & NICU are synonymous for us.

Also, on your first scenario, since we don't have the 2012 data in this spreadsheet, we really wouldn't know if the baby was in the SCN/NICU for the December days, without looking. I'm thinking for those patients who cross over into a new year, I would have to just manually check those to make sure that I'm counting it correctly, or add another tab with 2012 data. Either way, for your purposes, we will assume that we already counted the admit in December.

Thanks for the great questions/clarifications!

On that first example, I think you can be sure the baby spent some days in the SCN in 2012 because the admit date in col C is 2012-12-24. (Christmas Eve, he muses. I entered the hospital Christmas Eve of 2010 and didn't get out 'til Mother's Day; swine flu.) But it doesn't matter; the relevant factor is that the admit date isn't 2013, so the patient isn't to be included in this count.

Now, about the 3rd example: I get it, the policy is to assign a new patient number in such a case. (I thought you might say that, in fact.) But like all old and grizzled programmers with decades of experience, I am untrusting. However impossible it may seem to you that a patient be admitted a second time with the old patient number, I nevertheless must ask: If the impossible should happen, how should it be counted? Three possible answers: If the patient is readmitted with the same patient number...

1. Count it as 2 admissions.
2. Count it as 1 admission.
3. Call it in some way to the attention of the user—you or whoever—by displaying a message, highlighting it in a new color, whatever.

The way we do this will depend on your answer above. Hint: If you're really determined to believe that it cannot happen, and therefore don't waste any time thinking about it, then answer #2 is probably the easiest.

I haven't asked this yet, but what's your final output? Are you looking for just a number (244 admissions)? Do you need a list of 244 rows, one for each patient showing the admit date and maybe the discharge date? What?

One last consideration: How shall we do this? If you have to do this only once a year, then I'm guessing it isn't worth the trouble to write a VBA program for it; I can just show you how to massage the date with a sort and some functions to come up with the result. If I've misunderstood and you need to do this a lot more often—maybe you want to do the same thing to 43 other worksheets, once this one is done—then a VBA program is probably better.

I can not always go by the patients' admit date, due to the fact that if the baby isn't a sick baby, we don't get to count that admission. So, it is quite possible that the baby could have worsened a few days after being born, and then had to be moved to the SCN/NICU. Like I said, those are my exceptions, and I will just know to look for those type of scenarios, and correct my data accordingly.

Since we should NEVER admit a patient back to the hospital, using the same account #, I would prefer that those are flagged, so that I can bring it to our admitting department's attention - this would have to be corrected on their end. SO...we can just highlight it, and then I will follow up. I do not foresee that this would be an issue.

We calculate days, admits, etc. on a daily basis, for all of our patients. For the SCN, that department was keeping manual stats that had to be reported once a week, which included # of admits, days, and now our administrators want it compared to last year as well. So, I have dumped in all of 2013, and will be dumping in the 2014 data on a separate tab. I would need to see admits and days (days would be just a count, by month, for every day they are housed in the SCN/NICU). Our final output wouldn't include patient account #'s - just totals by month.

Wait, I'm missing something. Isn't the patient number in col B the baby's ID? I mean, the patient is the baby, not the mother, right? And isn't col C the date the patient entered the NICU? Apparently one or both of those assumptions are wrong, if you can't always go by the admit date.

About desired output: What I'm hearing is that it worked alright to do this manually on a daily and weekly basis, but a) to do the comparison for a whole year's worth you need Excel to do a lot of the work for you, and b) since you're doing that you may as well make it work for you on the weekly stats too. So we're talking about two kinds of output: A comparison of 2013 with 2012, and (if it can be done conveniently) a better way of doing the weekly summary. And for that matter you have to collect daily numbers too. But first things first: The whole-year comparison is the one that would cost you the most effort.

Now, I'm hearing two different things about that:

1. "....manual stats that had to be reported once a week, which included # of admits, days, and now our administrators want it compared to last year as well". That sounds like a week-by-week count of 2013's admissions and number of days' stay, compared week-by-week with 2012.
2. "Our final output wouldn't include patient account #'s - just totals by month." Not by week, after all, then?

And you want "a count, by month, for every day they are housed in the SCN/NICU". But since this isn't by patient, I guess what you want is the total patient-days. For example, if patient A stayed 12 days and patient B stayed 5, that's 17 patient-days and that's all you want for the "days" part of this?)

Sorry for the delay in getting back to you - things have been crazy busy at work.

The patient # IS in column B, and all of these patients are the babies #, not the mom's #. Column C is NOT NECESSARILY the day they entered the NICU - that would be column J the first time that account # appeared. It is possible that baby was admitted into the mom's room, on the day of birth, but 2 days later took a turn for the worse. In that case, the admit date would be, for example, 2/22/14, but the first day they came to the NICU was 2/24/14, which would be what shows up in column J. Now, if they are still in the NICU on 2/25/14, column C would still say 2/22/14, and column J would now say 2/25/14. Basically, column J just shows every date of service that they spent in the NICU.

As far as the desired output: We are going to keep up with this spreadsheet on a daily basis - loading the new babies that entered the NICU on the previous day. Our CEO would like to see, probably by month, the # of babies for each year (2013 & 2014), with a variance column. It would also be nice to see what the average length of stay would be. (We would have to count the # of times the babies account # appeared). If a baby is in the NICU for more than a month, and if we are reporting on January, we would only count up January's days in the January comparison, and then the February dates of service in the February comparison. Does that makes sense? The other thing that we have to report is how many admits we had in a particular month. If the patient was admitted to the NICU in January, but we are reporting on February, we would NOT count the admit in February, since we are only allowed to count an admit once for their hospital stay. I had originally thought I could say if(C=J,1,""), but since there are times that baby stays in mom's room the first day, it would never count that baby as an admit to the NICU. I'm not sure how we can capture those types of babies.

Did I clear things up, or do you still have questions? Thank you for your help!

IMO all this discussion of what the data are and how they're derived seems to be focussing on irrelevancies!

Assuming the data to be counted are in column B, you could use the following formula to get a 'unique items' count (i.e. each item is counted only once) in any other column:
=SUMPRODUCT((B$3:OFFSET(B$3,COUNTA(B:B)-1,)<>0)/COUNTIF(B$3:OFFSET(B$3,COUNTA(B:B)-1,),B$3:OFFSET(B$3,COUNTA(B:B)-1,)))-1

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I entered that formula into my spreadsheet, and it brought back 0 in every cell.