Merging multiple rows of patient info into a single row



I have a file which has multiple rows of info per patient.

How can I use a VBA (or another way) to move all info to a single row per patient (matching on the patient ID).

Attaching a simplified version of how the start and finished files would look (the colors are just to highlight for you).  Actual file will have over 3000 patients (potentially >9000 rows).

Thank you.  Any help is much appreciated.




I'm not at all sure that doing this without VBA was really easier for me but I'm confident that it will be easier for you, at least, after I did all the heavy lifting (for me it was heavy) LOL:

The idea I implemented was to complement the data in all first rows with the blood type which is usually in the second. Then all seconds are marked by removing the patient ID. This is accomplished with worksheet formulas.

In the next step, lines without patient ID are removed using a filter. The filtered result is copied to another location. The whole process is documented in 12 easy steps in the attached workbook. It took me 2 hours to it set up and will take you 2 minutes to execute, hahaha. Good luck!

Edit 21 May, 2020   ==========================

I didn't know you need to repeat this on multiple workbooks. The solution I offered will give you gray hair if you try to  implement it using the macro recorder. Therefore I added code to the workbook. It's in the standard code module TXL_4358 and works on the "Input" sheet.

It took me a fraction of the time I needed to prepare the instructions for the codeless solution.



Wow, thanks for your efforts.  What is the index function in step 3 doing? After I understand I will try to record a macro to do this because I have to do it multiple times a day on different files. Thanks again.
dsmith41 May 20, '20 at 1:54 pm
In step 3 the blood group is found and added to the last column of each record if it exists. The formula is so long because it, in essence, says "If you can find the blood group in this way Then find the blood group in this, else nothing", meaning the same search is conducted twice. This is to suppress the return of 0 which could also be done by cell formatting, allowing the formula to be cut by 55%.
I've added code to my anbswer. Please download the modified workbook.
Variatus (rep: 3838) May 20, '20 at 9:03 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login