Sorting problem

  • Thread starter Thread starter The Bodster
  • Start date Start date
T

The Bodster

Please help me out, I need cheering up!

How do I sort a sheet (say 10 rows of 5 columns) into 1 row of 50 columns,
where each 5 columns in the new single row is made up of the successive rows
of 5 columns of the original data? I don't want to cut and paste.

Cheers,

B.
 
Hi
in A1 on your second sheet enter the following formula:
=OFFSET('sheet1'!$A$1,INT((COLUMN()-1)/5),MOD(COLUMN()-1),5))
and copy this to the right
 
Frank,

Thank you so much, this will save me a lot of time.
Some more questions?
1. How can I apply the same procedure to lots of groups of 10 rows, so
that in Sheet2 row2 the comments are those from rows 11-20 of sheet1 etc..?
2. What happens if the data in sheet1 is in groups of 6 or 8 rows, how
would the procedure change then?

Once again thank you,

B
 
Hi
1. Use:
=OFFSET('sheet1'!$A$1,INT((COLUMN()-1)/5)+(ROW()-1)*10,MOD(COLUMN()-1),
5))

2. You have to change the /5 and ,5 parts accordingly
 
Back
Top