Problem with list where a single record has data in several rows instead of one row

Data downloaded from other systems into excel sometimes come in the form where the data pertaining to a single record is in several rows. The problem is made worse when the number of rows of data pertaining to each record is not the same. For eg, the 1st record may have data in 3 rows, the 2nd record with data in 5 rows and the 3rd record with data in 4 rows and so forth. Is there any formula or efficient way to arrange the data pertaining to each record all in one row for easy sorting and analysis?

I may not be very clear so I've attached a file to illustrate what I mean.

Many thanks for your help!