Data from multipe rows converted into a single row

Hello

I have been scouring help forums trying to find a solution to fit my particular data set. I have installed the MorFunc addin and have tried several macros applications, but to no avail. To put it simply, I have about 2000 rows of data for approximately 900 unique subjects, it


I have data in the format attached, although some subjects have as many as 5 blood draws (in the example they have a max of 3 blood draws)

I have.xlsx

I need it in this format, with all of the blood draws in a single row for each individual subject id.
I need.xlsx

I can get examples of various techniques to work, but I have not been able to translate any of it to my data.

Thank you for your help!
Clelia

Ok


We can do this using a sort and some formulae,
We can use a macro to automate that

Or we can write a dedicated macro which is harder to understand and modify, but could be faster.

Which do you prefer?

I am more adept at using formulas than I am with macros, so perhaps that is the best option. Also, I predict that this issue will come up again and again with different types of data so being able to modify it myself is important.

Thank you for your rapid reply,
Clelia

K

I have recorded myself doing the sorting an the formulas.

Now I have to tidy it up.

BRB

Try this Macro.

It should be easy to understand if you step through it.

Thank you mehmetcik, I will try this first thing in the morning and let you know how it work!

I have tried it and it works well on the data that I gave you, however when I run it on my original dataset, it will only give me 2 BloodDrawId2 and AgeMo3... I'm not sure why. I have attached a workbook with two pages- one after I ran the macro and the original set of data. You can see even the SubjectId from my previous example only has 2 BloodDraws and 2 AgeMo-- even though when run on the smaller set it works perfectly. tocolumns-ranMacro.xlsx

Thank you!

My apologies an error crept in there.

Try running macro1 in the originaldata worksheet.

This works beautifully! Thank you for your quickness and aptitude!

Thank you popipipo.. I will check this out, I always like to have more than one approach to a problem.

Hello

Can you tell me how I might edit the code to include more columns of data?