Formula for converting a long row of cells into multiple rows

[ntmdv1]

I cannot figure out a formula to convert the data from looking like this:

Column: C D E F G H I J K L M N O P Q
Row One 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

and make it look like this starting in cell R1:

Column: R S T U V
Row One 1 2 3 4 5
Row Two 6 7 8 9 10
Row Three 11 12 13 14 15

Can anyone help with this? Need a formula, macro will not work.

Thanks!

[Pete_UK]

Put this formula in R1:

=INDEX($C$1:$Q$1,COLUMNS($R:R)+(ROWS($1:1)-1)*5)

then copy across and down.

Hope this helps.

Pete

[ntmdv1]

Yes this works, thanks Pete!

Now, is there any way to have the formula repeat breaking down the long row of cells in C2 into 3 rows, then C3, etc. all the way to C15?

Many thanks!

[Pete_UK]

Do you mean C7, D7,E7, then C8, D8, E8 and so on ? (Those are columns that it is going across).

If so, put the formula in C7, change the 5 at the end to 3, then copy across and down.

Pete

[ntmdv1]

Sorry, I edited my prior post to the right cells. Hopefully it makes more sense in what I am trying to do.

[Pete_UK]

Attach a sample file showing what you want to achieve (and in which cells), as I can't really understand your edited post.

Pete

[ntmdv1]

So essentially:

Column: C D E F G H I J K L M N O P Q
Row One 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Row Two 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
and make it look like this starting in cell R1:

Column: R S T U V
Row One 1 2 3 4 5
Row Two 6 7 8 9 10
Row Three 11 12 13 14 15
Row Four 16 17 18 19 20
Row Five 21 22 23 24 25
Row Six 26 27 28 29 30

...and keep repeating that format through row C15 of the original data set?

[ntmdv1]

Pete- Hope this helps.

Thanks again!

[Pete_UK]

Okay, put this in R1:

=INDEX($C$1:$Q$15,INT((COLUMNS($R:R)+(ROWS($1:1)-1)*5-1)/15)+1,MOD(COLUMNS($R:R)+(ROWS($1:1)-1)*5-1,15)+1)

then copy across to V1, and down as far as you need.

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, you might like to know 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