Merging rows, patient database

**jcexceljc** · 09-08-2017, 05:19 AM

Hi everyone!

I registered here after many hours of frustration. I am not an adpet user of excel, but was earlier this week handed a database of a few thousand patients that i need to work with. Ive worked out most of the problems, and am quite happy to have learnt a bit of excel on the way. Now however, i am stuck with what seems like a simple problem.

The data is in the following format:

I ID 1 I AGE I *** I test:BACTERIA I POSITIVE/NOT POSITIVE I DATE I

I ID 1 I AGE I *** I test:TOXIN I POSITIVE/NOT POSITIVE I DATE I

I ID 1 I AGE I *** I test:TYPE I TYPE I DATE I

I ID 2 I AGE I *** I test:BACTERIA I POSITIVE/NOT POSITIVE I DATE I

I ID 2 I AGE I *** I test:TOXIN I POSITIVE/NOT POSITIVE I DATE I

I ID 2 I AGE I *** I test:TYPE I TYPE I DATE I

So for every ID/patient there are atleast theese three tests, and on some patients each test is done several times, resulting in even more rows. A single ID/patient might have 10 rows, one for each test. Now i want to merge this data, to only one row per patient, but im at a loss on how to do it.

Any advice would be greatly appreciated!

**Syrkrasi** · 09-08-2017, 10:59 AM

Jcexceljc Welcome to the forum

Please be so kind and attach a sample Excel workbook.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

**jcexceljc** · 09-08-2017, 04:24 PM

Thank you for your reply! I feel much more hopeful for a solution after reading it. I will not be able to access the data over the weekend, but i will post according to your instructions on monday!

**jcexceljc** · 09-18-2017, 05:51 AM

I've been busy trying to sort out various problems, but now im finally back to my data! I've created an anonymous sample of the original data, as well as a small book showing how i would like it to look. I Hope this makes sense to you

Basically there is a row for every analysis, but i want a row for every patient, with a column for the results of each analysis. Some patients have done more than one test, in which case i only want to use the data from the first (oldest) test.

**Richard Buttrey** · 09-18-2017, 06:18 AM

I'd be inclined to add helper columns to your data.
One column each to identify whether
MALDI
Påvisat (från prov) (1/0)
Påvisat (från framvuxna bakterier (1/0)
GRÄNSVÄRDE (från prov)
GRÄNSVÄRDE (från framvuxna bakterier)

are present in column F, although I don;t understand where the (1/0) references come from, and an extra column that calculates and marks which row is the oldest date for each analysis.

Once you've added the helper columns then a Pivot Table will give you the analysis - use Slicers to make date and other filters easily configurable.

**Syrkrasi** · 09-18-2017, 04:02 PM

Here is may take on your request with the data given as-is

1. I used Name Ranges instead of Column & Cell references:

Cells A2:A604 = ID

Cells B2:B604 = AGE

Cells D2:D604 = INDATE

Cells F2:F604 = RESULTS

2. Below are the formulas used to capture the results you requested

To capture the Ålder: =IF(ISBLANK(A2),"",INDEX(AGE,MATCH(A2,ID,0)))

Note: this is an array formula, so you must press the Shift + Ctrl + Enter

To capture the Provdatum: =IF(ISBLANK(A2),"",INDEX(INDATE,MATCH(1,(A2=ID)*(B2=AGE),0)))

Note: this is an array formula, so you must press the Shift + Ctrl + Enter

To capture the Påvisat (från prov) (1/0): =IF(ISBLANK($A2),"",SUMPRODUCT(COUNTIFS($A2,ID,$D2,INDATE,K$2,RESULTS)))

Note: this formula is not an array formula, so just press the Enter key

To capture the Påvisat (från framvuxna bakterier (1/0): =IF(ISBLANK($A2),"",SUMPRODUCT(COUNTIFS($A2,ID,$D2,INDATE,K$3,RESULTS)))

Note: this formula is not an array formula, so just press the Enter key

To capture the GRÄNSVÄRDE (från prov): =IF(ISBLANK($A2),"",SUMPRODUCT(COUNTIFS($A2,ID,$D2,INDATE,K$4,RESULTS)))

Note: this formula is not an array formula, so just press the Enter key

To capture the GRÄNSVÄRDE (från framvuxna bakterier): =IF(ISBLANK($A2),"",SUMPRODUCT(COUNTIFS($A2,ID,$D2,INDATE,K$5,RESULTS)))

Note: this formula is not an array formula, so just press the Enter key

* As Richard Buttrey had pointed out, you'll need a helper column that list the naming convention as it is in column F of Blad1

See Attachment

Merging rows, patient database

LinkBack

Thread Tools

Rate This Thread

Display

Merging rows, patient database

Re: Merging rows, patient database

Re: Merging rows, patient database

Re: Merging rows, patient database

Re: Merging rows, patient database

Re: Merging rows, patient database

Thread Information

Users Browsing this Thread

Similar Threads

Macro (?) for importing a range of patient data and printing pages for each patient

[SOLVED] listing and alphabetizing patient names always populates 1 less patient than what I have

Save Patient Records As Database System

How to extract data from Excel database to create word report for each patient

Merging rows w/ duplicate values to import to database

Matching patient personal data to that already stored in a database

[SOLVED] de-identifying medical record number in a patient database

Bookmarks

Bookmarks

Posting Permissions