Excel 2010 Previous Discharge Date & Time For Any Given (Re)Admission This Week 1

[PWD]

Good afternoon from the UK and happy voting to my chums in the New World!

I have attached an anonomised workbook with two worksheets; the first, 'InPatient Readmissions', shows patients who have been admitted to hospital within the reporting week - 2nd May to 8th May, inclusive, and the second, 'Inpatient Previous Discharges', are all the patients who were discharged from within 30 days prior to 2nd May up to and including 8th May as we need to identify those who are readmitted during the reporting week within 30 days of any previous discharge.

I think that I have managed to put together an array formula (Cell G2) that will get the Previous Discharge Date whether there was one or more Previous Discharge or whether there is one or more admission during the reporting week. I've got so far but I'm getting Discharge Dates returning from the current admission for some records and I'm reluctant to attempt to add more checks to the formula without some BIG BRAINS casting their eyes over it in case I've made it over-complicated already.

This is a new hospital system and I think that there are errors in the raw data - Rows 46 & 47 for example - and I'm not worried that this patient will be excluded.

Many thanks,
D€$

 

[SkipVought]

PWD, voting here in the New World across the pond, is NOT happy, regardless of which way you might lean! I have a clothespin on my nose!

You've got a honkin' huge formula. I'd be apt to write a UDF, where all my IFs could be well scrutinized to determine that I had all my possibilities covered. I'd make a test table to specifically exercise each condition and then run the test to confirm.

With such a spreadsheet formula, you certainly need to test each condition that you intend the formula to encounter.

Just my 2 cents.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Thanks Skip, we're fascinated by your democratic process but think that it's a bit of a lose/lose situation.

Yeah, massive formula. I'm not really familiar with UDF's. Have you any pointers to set me in the right direction? Would it be a question of breaking this formula down into chunks and putting each one in a Function and then testing each one in a cell?

Many thanks,
D€$

 

[SkipVought]

This has to be in one function.

Code each if...then...else...

'
   If expression1 Then

   Else

   End If

...then fill in the blanks.

Indent to make each section of code more understandable

'
   If expression1 Then
      If expression2 Then

      Else

      End If
   Else

   End If

I'm out of the office so thats about all i can suggest.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

I'm back.

1) Why aren't you using structured table references? you have FULL COLUMN references and then $A$2:$B$20000, when you only have 310 rows in that table?

2) you have a reference in row 2 in your first MATCH to L1??? while all other references are to L2. THAT's a problem.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Hi Skip. MR President. WOW!!!!

1) Never used Structured References before but I've now renamed the Tables and amended the formulae accordingly - thanks for that tip.

2) I'm looking to match the previous Admission Date Time (which is sorted oldest to newest) if the MRN's are the same, otherwise look at the current row - I think that works.

I'll give the UDF's a go too - in the fullness of time. Meanwhile I think I'll send off my CV with my bricklaying experience and see if I can get myself a Green Card!!

Many thanks,
D€$

 

[SkipVought]

Unbelievable! Seems this might be some sort of USExit toward populism vs socialism or conservatism.

I'm looking at your honkin' huge formula. Got distracted last night with 'other things' 🗳 but I'm gonna try to break it down. Perhaps I can or not, it's a definite maybe. Got some things to do this AM. Ta da.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Thanks Skip. Well, the sky hasn't fallen in, yet!

Many thanks,
D€$

 

[SkipVought]

I got sidetracked. Don't know when I'll get some time to look at your formula.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

If you retooled your tables and formula for Structured Table notation, would you upload that version of your workbook. Otherwise, I doubt that I'll spend any more time on this.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]