Manage and format raw data with VBA

Hi Gurus,

I've the following raw data exported from other system.

Wish to get the expected output data in this format, maybe with vba coding.

Sample Data:
STUD011264 789 TEACHER1 Math and science
STUD010874 789 TEACHER2 Drama and social studies
STUD010876 789 TEACHER1 Biology
STUD011006 789 TRAINER1 Basketball and tennis
STUD011008 789 TEACHER4 Geography and history

Sample Output:
STUD011264, STUD010874, STUD010876, STUD011006, STUD011008
-> Output in first row

Math and science, Drama and social studies, Biology, Basketball and tennis, Geography and history
-> Output in second row

Apperciate for your help.

Thank you.


- Jack

Try this..

Select you data > Copy it using Ctrl+C

On a couple of rows below paste using Ctrl+Alt+V > E

Then re-arrange rows as required..

Let me know if that solves..

Hi Gurus,

I've attached a sample data in this post.

Sample of output data are highlighted in light blue.

Thanks for your help.


- Jack

Hi Gurus,

Thanks for your reply.

The sample data that I've posted is only a small portion.

I usually have a very long listing, maybe about 20 to 30 items at 1 time.

So, would prefer if it can be done using some kind of VBA coding.

Thank you.


- Jack

Hi Gurus,

Anybody can help me please?

Thanks.


- Jack

Hi jindon,

Thanks for your reply and help.

It works almost perfect .... need to check if it can be enhanced to cater for more entries eventhough the actual entries are not that many, see below.

=JoinLR(A1:A100,1)
=JoinLR(A1:A100,0)


Thanks.


- Jack

Hi jindon,

Ok, maybe I can try to put it in this way, will it be possible to make the code to be more dynamic? Meaning, it'll be able to automatically cater for x number of entries that is provided? It could be shorter, but it could also be longer.

As per my sample data, I've only given a sample data of 5 entries.

Thank you.


- Jack

Hi jindon,

Great, it works perfectly.

Appreciate for your help.

Thanks.

Hi jindon,

Done. Thank you.

Have a nice day


- Jack