Multiple lines of similar data to one single line

**T_Van** · 02-27-2012, 01:48 PM

All,

I have multiple lines of data (patient data), where the patient name, registration time and discharge time are the same, but the diagnosis code is different. I am trying to get to a point where the final list has one line for each patient/reg time/disch time are the same, and have additional columns for each diag code. Look at this example:

Name Reg Time Disch Time Diag Code
Joe Smith 10/1/2010 10/2/2010 401.1
Joe Smith 10/1/2010 10/2/2010 402.1
Joe Smith 10/1/2010 10/2/2010 403.1

My finished product would look like this:

Name Reg Time Disch Time Diag Code1 Diag Code2 Diag Code3
Joe Smith 10/1/2010 10/2/2010 401.1 402.1 403.1

Is there a way (using formulas, etc.), that I could do this relatively quickly? I have 104,000 lines of data that I need to sift through.

Any help would be appreciated.

Thanx,
T_Van

**JieJenn** · 02-27-2012, 02:02 PM

I am assuming each patient has 3 Diag code. I put a formula in the sample file to extract the data. What you might want to do is 1) Copy and paste all formula into values, then use autofilter to delete all the blanks.

**T_Van** · 02-27-2012, 02:39 PM

JieJenn,

Thanks for the quick response!

There may be some lines that have 1 diag code, some that have 2, and a few that may have 4 codes. Will this formula set-up work in a similar fashion?

Thanx,
T_Van

**JieJenn** · 02-27-2012, 03:33 PM

If you post a sample file I can probably write a macro for it.

**T_Van** · 02-27-2012, 04:10 PM

JieJenn,

I have some slightly good news - we can use one particular field (encounter ID) as the basis.

I have attached a list of 229 lines with 2 columns: encounter ID and ICD9 (Diag code). My goal is to have a list of data with one instance of each encounter ID, and separate columns (or one column) with all of the associated diag codes on that same line.

I appreciate any help you could give.

Thanx,
T_Van

**JieJenn** · 02-27-2012, 04:23 PM

So is your company hiring? haha.....

**T_Van** · 02-27-2012, 05:37 PM

That worked! Thanks, JieJenn...

You'd have to relocate to the Detroit area. Be careful what you wish for...

T_Van

**JieJenn** · 02-27-2012, 06:03 PM

Oh darn. I didn't know Chesterfield is that close to Detroit. Just moved to SF from Chicago like 6 months ago and I thought I still remember Michigan. Guess not.