data organization

I have large set of data in vertical form. One pr ID have one or more records against each. i want to organize the data as, the PR ID should appear one time and there records may shown horizontally one by one. I have tried vlookup, pivot table but not find required result. Anybody have idea through macro, formula or other method to solve this. This will increase my knowledge. I will be thankful for the help. The example is show below and file is also at



1938 10.04.2012 10.04.2012 Tech IIT
1938 15.12.2010 15.12.2010 Safety Tech IIT
1938 18.02.2013 18.02.2013 Safety Tech IIT


Pers.No. Start date End Date Training Institute Start date End Date Training Institute Start date End Date Training Institute
1938 10.04.2012 10.04.2012 Tech IIT 15.12.2010 15.12.2010 Safety Tech IIT 18.02.2013 18.02.2013 Safety Tech IIT

I'll get back to you later on with a formula solution.

(Just going out now).

Pete

Waiting for your effort. Thanks for taking interest in my post.

The attached file shows how you can do this with two formulae (shown in blue). First, put this one in F4:

Formula:

Please Login or Register  to view this content.

and copy down to beyond your data. Then put this one in K15:

Formula:

Please Login or Register  to view this content.

then copy across and down as required.

Hope this helps.

Pete

Thank a lot for your kind immediate help to solve my problem. I like your formulas. I have understand the first formula but unfortunately i cant understand the second one fully. I shall be grateful If you may kindly explain the second formula enable me to understand it. One again thanks for your help

Well, you can see the effect of the formulae even if you don't understand them. The first formula gives these results:

1938_1
1938_2
1938_3
2026_1
2327_1
etc

This gives each of your records a unique reference. The second formula takes this reference and transposes the data onto the appropriate row and into the appropriate column. There are 4 fields in your original data, so we need to take a block of 4 cells and then move down to the next row of original data. To understand the formula better, put this part into K12:

=INT((COLUMNS($K:K)-1)/4)+1

and this into K13:

=MOD(COLUMNS($K:K)-1,4)+1

then copy both of these across - can you see how they relate to the rows and columns that we want to get out data from?

Hope this helps.

Pete

Tulips.jpgThanks a lot for great help which is highly appreciable in term of learning and teaching. My discussion experience with you is very nice. God may bless you for your kindness.

Thanks for the flowers, and glad to hear that worked for you.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete