Splitting a list of data into multiple columns

Thanks for looking at my thread. I have a question about Excel. I apologize in advance if I don't do a great job of explaining what I am trying to do, it is kind of difficult to explain..

I am using Excel 2007.

I would like to be able to input a list of text data and have Excel split the list up into different columns. I would like to be able to set a maximum number of cells to use per column. That way, once the list has reached the limit, the next piece of data would be at the top of the next column, and the process would be repeated until there was no more data left.

For example, if I have the following data:

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O

I would like to be able to set a maximum number of cells per column. If I chose to allow a maximum of 5 cells per column, the output would look like this:

A____F____K
B____G____L
C____H____M
D____I_____N
E____J____O

I would like to be able to keep the data in the order in which it was inputted.

Does anybody know how I could do this?

Thanks

How about the attached:

Note:
The 7 in the formula stems from the fact that the table starts in column G, which is the 8th column
The formula multiplies the table column with the entries per column to arrive at the required row offset.
If you copy this to your own spreadsheet, you need to adjust the 7 to reflect the start column of your grid.

hth

PeterGriffin,

Welcome to the Excel Forum.

With your data in column A, beginning in row 1.



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Then run the "SplitData" macro.

There you go, Peter! Two completely different approaches. One with and one without VBA. All delivered within 90 minutes.

You've gotta love this forum!

Then again.... someone's sure to pipe up about volatile etc, so, instead of the volatile offset formula,

=IF(ROW()>$E$1+1,"",OFFSET($A$1,(ROW()+(COLUMN()-7)*$E$1)-2,0))

you can also use the non-volatile index version.

=IF(ROW()>$E$1+1,"",INDEX($A:$A,(ROW()+(COLUMN()-7)*$E$1)-1))

If your spreadsheet is big and the formula has to crunch a lot of data, you may notice a difference.

But probably not.

Originally Posted by Teylyn

...someone's sure to pipe up about volatile etc,...If your spreadsheet is big and the formula has to crunch a lot of data, you may notice a difference.

But probably not.

I'd simply quote Charles Williams:

Avoid volatile functions wherever possible

and to be frank I'd take his advice on the subject of XL Calculation over anyone else's.

Darker0ne,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

See post #8.