Forum Discussion
albatros8
Jun 20, 2024Copper Contributor
Excel multiple columns to rows
Dear, I would kindly ask for help. I'm using Excel 2013. I have 16 columns and big numbers of rows (e.g. 300+) I would like that each row, would be placed next to the ending (16th) column. ...
HansVogelaar
Jun 20, 2024MVP
Using a formula: on another sheet, enter the following formula in A1:
=INDEX('Sheet 1'!$A$1:P$300, QUOTIENT(COLUMN()-1, 16)+1, MOD(COLUMN()-1, 16)+1)
where Sheet 1 is the name of the sheet with the data.
Fill or copy to the right.
Another way would be a VBA macro. Let me know if you prefer that.
albatros8
Jun 20, 2024Copper Contributor
This one is not working for me... I don't know why. I'm doing something wrong, or the versions (Excel 2013) doesn't have the same functions or signs, etc...
- HansVogelaarJun 20, 2024MVP
- albatros8Jun 20, 2024Copper Contributor
Hay.
Now it works. The signs were different. It must be ";" instead of ","In this case, I need to expand the formula to get all the data.
Thank you.- HansVogelaarJun 20, 2024MVP
albatros8 If you use comma as decimal separator (two-and-a-half is written as 2,5), then you should use semicolon ; between the arguments of a function.
If you use point as decimal separator (two-and-a-half is written as 2.5), then you should use comma between the arguments of a function.
Most users here will assume that you use the latter unless you explicitly specify otherwise in your post.