Excel 2010 - Find a Match Of Any Row of Diagnosis Codes For Each Reference Number 1

[PWD]

Good morning, I am trying to find patients who have reattended the Emergency Department with the same diagnosis as their previous visit. I have reached the point where I have identified the patients and most of them have more than one Diagnosis Code, resulting in more than one row. I can't think of a way to create a formula that will find all the matches of Patient Reference Number and check to see if any of their current Diagnosis Codes match any of their previous Diagnosis Codes.

Any ideas or suggestions?

Many thanks,
D€$

 

[PWD]

I can find the first, Previous Diagnosis Code with this formula:

=IFERROR(INDEX('A&E Previous Discharges' !E:E,MATCH('A&E Reattendances'!E2,'A&E Previous Discharges'!D,0)),"")

This finds the FIRST Previous Diagnosis Code from the Previous Attendance Encounter ID in the current attendances matching the Encounter ID in the previous attendances but that doesn't appear to really help me much.

Many thanks,
D€$

 

[SkipVought]

HI,

Do you think that providing more information and data might help?

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

 

[PWD]

Hi Skip, it's 10:10 p.m. now but when I get back to work in the morning I'll get the workbook uploaded.

Many thanks,
D€$

 

[SkipVought]

THAT will get you some answers.

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

 

[PWD]

Hi Skip 08:55, here's the file. These are all the people - their Medical Record Numbers have been anonomised - who reattended the ED within 7 days of their previous Check Out Date & Time. As you can see, most of them have been many Diagnosis Codes - I have manually entered some of them as, originally, they weren't output. In our (new) system it is possible to designate more than one Diagnosis as a Primary Diagnosis so I have not been able to simply use 'WHERE([Diagnosis Code Sequence]=1)' - which had been my first thought.

What I'd settle for now is just a way of seeing if any of each patient's Diagnosis Codes on the worksheet 'A&E Reattendances' are also present in that patient's list of Diagnosis Codes on worksheet 'A&E Previous Discharges'.

Many thanks,
D€$

 

[SkipVought]

Its %:42 here.

I can't find Patient Reference Number or Medical Record Number, so I don't know for sure (I could GUESS) what you want!

Please restate the problem with terms that can be identified in the data you provided with specific reference to one item that has a grouping that you intend to generate.

ALSO, this is a .xls (97-2003) version, yet the A&E Reattendances tab has a Structured Table (Table1). Don't quite understand that.

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

 

[PWD]

Good morning Skip!! My apologies "Medical Record Number" get abbreviated to "MRN", in this case "MRN-Organisation" - "Patient Record Number" was my initial, generic, descriptor.

Problem:
I have identified MRN's of patients who attended ED "this" week and had previously attended and been checked out within the previous 7 days (604800 seconds, to be precise!) of this current attendance Date & Time.

Within this cohort I need to obtain a subset who reattended for the same reason - identified by having the same Diagnosis Code recorded. Ideally this should be the same 'Primary' Diagnosis but, as explained there can be more than one 'Primary' Diagnosis which makes it difficult to construct the query in the first place. Even more so when there are patients for whom NO Diagnosis has been identified as 'Primary'.

So, at this stage I'd be happy to find a match, or otherwise, for each patient in the array of Diagnosis Codes for the current attendance within the array of Diagnosis Codes for the previous attendance.

Many thanks,
D€$

 

[SkipVought]

What about the Structured Table vs .xls (97-2003 version mismatch)?

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

 

[SkipVought]

How about using a conditional format that matches previous diagnoses codes?

=MATCH(D2,OFFSET('A&E Previous Discharges'!$A$1,MATCH(A2,'A&E Previous Discharges'!A:A,0),4,COUNTIF('A&E Previous Discharges'!A:A,A2),1),0)>0

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

 

[PWD]

Definitely a mismatch! I obviously don't follow my own Standard Operating Procedures, oops.

Wow, now that's a doozy!

I can see what that does too!

Many thanks,
D€$

 

[SkipVought]

Does that meet the entire objective or part or none?

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

 

[SkipVought]

Here's your workbook.

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

 

[PWD]

(Edit] Just came back to this after composing my post and saw your later post.

Hi Skip, well if I filter on the Conditional Formatting colour I can see the MRN's that do have a matching diagnosis anywhere/somewhere.

I thought of using SubTotals to separate each MRN group but it appears as if I have to convert the Table back to a Range to permit this.

I then wondered if I could somehow use a 'Visible Cells' formula to count the rows where the word "Total" now appears:

So, what I've come up with is:

Apply Conditional Formatting to Column D - Diagnosis Code
Filter Column D on the Conditional Formatting colour
Insert SubTotals based on change on MRN, (SUM on final Column Month A&E Readmissions, but I'm not sure if that's relevant)
And then use a formula to count those rows that contain "Total"

=COUNTIF(A2:A264,"*Total")

This will give the sub-cohort of those who reattended "for the same reason".
Select all from Column D
Apply the SubTotals again and this will give the total number of patients who reattended.

A bit convoluted but it appears to be a full solution .

Actually I've got a bit more refining to do as it's possible that a person could have attended more than once in this week as well. Grrr, I think I've got the measure of it though.

Many thanks,
D€$

 

[SkipVought]

???

1) Why not ALSO filter on the MRM of interest.

2) Insert a row directly above the table for aggregations. Use the SUBTOTAL() function to aggregate VISIBLE rows.

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

 

[PWD]

Hi Skip, yes I had considered SUBTOTAL(3,A:A) but I just need a count of the individual MRN's - 88 - then the count of those with a matching Diagnosis - 24.

Many thanks,
D€$

 

[SkipVought]

You may need to build a UDF.

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