Help merging rows

[Melot]

Hi! I'm currently working on a report in med. school and have gotten hold a large database consisting of almost 10 000 patients and 80 000 rows in excel. However, most of these row would be unnecessary if I could just merge them together.
For instance: If a patient takes seven blood tests, each of these are stored as one individiual row instead of storing all test results on the same row but in different columns.

These pictures will hopefully explain better than I do:
http://oi59.tinypic.com/3wvbn.jpg
http://oi59.tinypic.com/2j2z2gh.jpg

First one is how the data looks right now. Second is how I want it to be. Notice though that a new row, for the same patient, but with a different date is ok! As long as all the blood test results taken on the same day ends up on the same row.
I'd be incredibly grateful if this was possible and if someone could help me

[mikeTRON]

Can you post a sample dataset instead of images?

[Melot]

Sure, I'll give it a try!

Here's a sample of the first 10 patients. Recoded ID's, dates and ages of course for confidential purposes. But otherwise it's identical to the actual database I work with.
Thanks for giving it a go!

Sample_data_20141017_MB-1.xlsx

[mikeTRON]

can you post a sample data set and a proposed solution.

Let us see exactly what you are trying to do.

[Melot]

Not quite sure I'm bright enough to follow... I thought it was a sample dataset I was posting.
I'll attach the sample workbook from Excel and a "before" and "after" sheet of how the data is and how I want it to be.

Hope it will make things clearer. And thanks alot for trying to help me!

[Melot]

I've tried learning some VBA and messed around with the data. I found some code in another forum and tried to edit it to fit my purpose. I feel like I'm a bit closer, but it still doesn't do what I want. It seems like it merges cells with empty data together with the test values, and the result is that the values are deleted and only the empty cell remains. Does anyone have a clue what I'm doing wrong?


I'm using this code:

Please Login or Register  to view this content.

And as always: I appreciate all the help I can get so thanks in advance!

[Melot]

I kinda solved it myself. The code isn't as beautiful as it could be and I'm sure it could be done in a better way. But it did manage to compress almost 80 000 rows into 20 000 (though I'd hoped for it to be closer to 10 000). Anyway, I felt I should post the code here. Someone else might find use for it.

Please Login or Register  to view this content.