200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi,
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.
