Iain Mac

I have a spreadsheet with a whole load of data in a single column that I need to turn into a table (so that I have four columns for Model, Size, Power, Price.)

The existing data gives me the info on 4 rows, then the next set of data starts - no empty lines.

My head hurts - help!

bil_s2

Not the most elegant method but will work...

Assuming your column of data starts in cell a1 and there are always 4 rows for each unique record in your table...

in cell b1 enter =a1
in cell c1 enter =a2
in cell d1 enter =a3
in cell e1 enter =a4
this will give you your first record.

copy cells b1..e1 to b5 - this will give you the 2nd record
copy cells b1..e1 to b9 - this will give you the 3rd record
repeat this a few times until you have half a dozen records.
Then copy b1..e21 to e25
repeat this process, copying all of the formula cells (the idea is you will double the amount each time you copy)

Once complete, highlight the entire worksheet; do EDIT COPY, then EDIT PASTE-SPECIAL, select VALUES and ok. This converts all of the formulae to values

Sort the entire range on column B and you will have all your records together.

Iain Mac

Thanks Bill

If you had replied just a wee bit quicker I would have done this - instead, I ended up with the offset command which did the trick but needed a bit of sanity checking.

Cheers all the same.