Merging two datasets: Duplicate records but only one contains data required

[JJ22]

I recently received a data extraction that was split into two parts. Basically one medical system ended recording patient vital information but continued to log the visit encounter. The other medical record system recorded the encounter information along with the vital information (weight, height, etc,.). Thus when I have tried to merge the data, I have rows with duplicate names, record numbers but only one of the rows having the vital information I need.

I currently have all the data in the same rows in one table. Alternatively, I also have the tables separated into two sheets with the exact same column titles.

Here is an example with two patients recorded in two different medical record systems. You can see the italicized informaton is what I am talking about; Allscripts EMR recorded the vital data and patient information but EPIC only recorded patient information.

Example.jpg

What is the easiest way to merge the data into one unique row per unique patient and dated encounter? Alternatively, one could have the information recorded in both rows.

Any help would be appreciated! I could manually do this by hand, but I have thousands of patients to get through so that isn't an option.

Thoughts?

[TMS]

Keep the tables separate and use VLOOKUP to copy the missing data from one of the tables to the other.

Then, Copy and Paste Special | Values to replace the formulae with text.

Be careful, because it looks as though the key field, patientmrn, is numeric in one list and text in the other. You'll need to ensure they're consistent.

Regards, TMS

[JJ22]

That is the thing, I am unsure how to do that.

[TMS]

You've uploaded a picture. I'm not planning on reproducing your data so I can make a set of formulae for you.

Put (a sample of) the two tables in separate worksheets in a workbook and upload it.

It should be easy enough to set it up for you from there.

Regards, TMS

[JJ22]

I'll fiddle with this small example. I think I may have been overloaded with all the patient cells before.

Let me know if that works, and I thank you very much for taking time to guide me in the right direction.

[TMS]

K2: =VLOOKUP($C2,ALLSCRIPTS!$C:$O,MATCH(EPIC!K$1,ALLSCRIPTS!$C$1:$O$1,0),FALSE)


Drag across and down. Format the cells with two decimal places.

Seems as though the patientmrn is consistent in this example so nothing to worry about.


Regards, TMS

[JJ22]

Ahh, I was on the right track.
=VLOOKUP($C2,ALLSCRIPTS!$C2:$O13,9,0)

I'll have to look up what all those other modifiers are doing.

[JJ22]

Well I hit a snag. The example I showed you was too simplistic. Most patients have multiple visits dates making a unique encounter per patient and visit date combo.

When I use =VLOOKUP($C2,ALLSCRIPTS!$C:$O,MATCH(EPIC!K$1,ALLSCRIPTS!$C$1:$O$1,0),FALSE) it will pull the patients first visit vitals which worked for the first example. But for the real database, the patient needs the corresponding visit date to match.

I altered the example and you may see what is going on. The duplicate SBP values are being pulled from the earliest visit date not the correct patient date combo. I hope this is clear on what I am trying to describe.

[TMS]

OK, try this array formula:

K2: =INDEX(ALLSCRIPTS!$K:$O,MATCH($C2&$H2,ALLSCRIPTS!$C:$C&ALLSCRIPTS!$H:$H,0),MATCH(EPIC!K$1,ALLSCRIPTS!$K$1:$O$1,0))

confirmed with Ctrl-Shift-Enter rather than just Enter. You should see curly brackets around the formula:

{=INDEX(ALLSCRIPTS!$K:$O,MATCH($C2&$H2,ALLSCRIPTS!$C:$C&ALLSCRIPTS!$H:$H,0),MATCH(EPIC!K$1,ALLSCRIPTS!$K$1:$O$1,0))}


Drag down and across.


I have to be honest and say that this could start to be tediously slow so it might be an idea to do, say, 50 rows at a time and then convert the formulae to values.


Regards, TMS

[JJ22]

I have been using this well, but I can't do more than 20 cells at a time without Excel triggering out of resources box.

Any ideas? Works as advertised. Lot's of processing even for my i7 ivy bridge processor.

Idea: Would it speed anything if I concatenated the date and and medical record to make one unique cell?

[TMS]

Would it speed anything if I concatenated the date and and medical record to make one unique cell?

Yes, absolutely ... I should have thought of that! That said, when I suggested it would be slow, I didn't think it would be that bad!

OK, on the ALSCRIPTS sheet, insert a new column K (before SBP, etc). In K2, put the formula: =C2&"|"&H2 (just makes it easier to see the two fields)

Then K2 on the EPIC sheet becomes: =VLOOKUP($C2&"|"&$H2,ALLSCRIPTS!$K:$P,MATCH(EPIC!K$1,ALLSCRIPTS!$K$1:$P$1,0),FALSE)

Copy down and across ... just a normal VLOOKUP formula.

Regards, TMS

[JJ22]

So I transfered all my Allscripts EMR sheet data into my EPIC sheet data because EPIC included additional parameters that are good to have. However, there is a problem.

The Allscripts EMR data contains patients and records unique to it, and the EPIC data as well. Furthermore, there is a time period of overlap where the encounter was recorded in both, but the data recorded in EPIC sheet which was resolved by the formula before. However, there are still unique patients in this time frame that weren't recorded in epic but in Allscripts. So after doing the mending, I have data both in EPIC and Allscripts that I need to take out and make into one database.

I see three parts:
1. Time frame in Allscripts prior to EPIC transition. These are unique Allscripts patients. Copy and paste into the new db.
2. Time frame of transition into EPIC from Allscripts EMR. This period has duplicate records from EPIC and Allscripts after doing the formula. It also has as well unique EPIC and Allscript encounters. I have attached an example of what it looks like. The 0.00 records in EPIC records are due to the formula finding a matching encounter in Allscripts but the record had no data.
3. Time frame Unique to Epic. Copy and paste into excel.

The question is how to resolve part 2, or simply, how to get rid of the duplicate values well. The way I see it in my mind is: check the medical record name and visit date, if they match, check the SBP, DBP, HT, wt, and BMI for a complete match. If they match, delete the Allscripts record that has less unique data. THis would make sure they are duplicate records as some records are blank but occurred on the same day.

---
I tried the initial formula again this morning, for some reason it didn't spur the error. I used office 2010 instead of 2013 preview however.

[JJ22]

Actually, just figured out a way.

I made a unique cell: the MRN, visit date, SBP, DBP, Ht, Wt, and BMI with =C2&"|"&H2&"|"&K2&"|"&L2&"|"&M2&"|"&N2&"|"&O2.

I then colored all duplicates which should show which EPIC and Allscripts cells have overlaping data. I then filtered cells unique to EPIC to leave only the Allscripts records.

I'll keep this thread going with my thoughts. That formula you gave me to concatenate worked out to be awesome!

[TMS]

For clarity, has your question been answered? Have you now got a mechanism for merging the data effectively? If so, you should really be marking the thread solved. If, after that, you wish to blog to it, I guess that's fine.

If you have more questions arising as you progress, you should be asking them in new threads.

Regards, TMS


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[TMS]

Thanks for the rep