Rearranging data to create a report

I am trying to format a report in an excel spreadsheet where each record in the original report takes up several rows of various sizes. Some records are only 1 row, others are up to 4 rows of data. While most of the record data (10 fields) are in the first row, 3 fields (quantity, item and cost) can have multiple entries each on their own row.

How can I arrange to move all the record data onto the first row so that each record takes up one only row?

Thanks for the response. I have attached a sample file.

I have this problem with quite a few of the reports which come out of our database as the reports have been designed to look nice when printed rather than make the data easy to manipulate. The solution I use is long winded but works. You can use it for now and maybe somebody will come along and have a better one.

Using your example. Select B16 to B44, then Find & Select -> Got to special -> blanks. You'll see that it has highlighted all the blanks between your rows with data and your cursor will be in B17. type = then using the cursor go arrow up then press control and enter and it automatically fills in your rows in between. Repeat in column D, then select columns B and D, copy and paste special value.

Do you need the info in G16, I16, G22, I22 etc?

It doesn't put it all on 1 row but it makes it easier manipulate.

Thanks. I'll give it a try.

I'm having a little trouble with the procedure that you have suggested. Can you clarify "type = then using the cursor go arrow up then press control and enter".

When you say using the cursor go arrow up. I'm not sure what that means. I keep getting the column filled with the cell address "=C16" instead of the data.