convert single column to multiple columns

How can I sort a single column of data to multiple columns of data. e.g. I want to convert ~1800 rows of data from 1 column to 3 or 4 columns.

I can't do it this way because of the way information is formatted. He cell contains a phrase. See example of data attached.

forgot to send attachment. Here is what data looks like:

A1: a sure thing
A2: above street expectation
A3: beat earning expectation
A4: below street expectation

Attached is example. Tab one is the data. Tab 2 is the desired result.

Guess I should have explained it better. The way it is working is the macro would look at the first 35 lines of data and place in page 1, column 1; the next 35 lines in page 1, column 3; the next 35 in page 1, column 5; next 35 in page 2, column 1; next 35 in page 2, column 5....and so on. Hope this helps. Thanks

I have attached updated file of how I want the data to look. Sheet 1 is the data. Sheet 2 is the desired result. I have highlighted the data indicating when a new column is to begin. I know I am only picking 35 rows at a time as this is the number of rows that will fit on one page at a time. It don't know of any other way to accomplish it, especially if the font and/or row width were to change (you could fit more or less rows on one page).

Any help is appreciated.bump

Can any one help me with this one? I've been trying for a while to fix but no luck. See attached file and comments. Thanks. bump

Can you just show me an example?

I can't open/download zipped file, can only see picture format file like jpg.

Jindon -

I have attached the file per your request. Let me know if you can't open file or have any questions. Thanks.

In your result sheet you need is to put a hardcoded reference to the correct cells in the data sheet. Or, have I missunderstod ? Is it more complicated ?

I have attached an example. In Sheet3 I entered the 5 formulas highlighted in yellow, and copied down to the rest of the cells.

It is possible to make one singel formuls that can be copied to the whole sheet, this choice depends on the ammount of source data you have. Is it to large to enter hardcoded refferences ?

In case you would like to know the formula for getting the right refferences automaticly, here it is:

Please Login or Register  to view this content.

Just copy this to every keyword cell in your result sheet.

Bjornar - Thanks for the formula. It works perfectly. Sorry to be a pain, but one more question. I am trying to modify the formula in two different ways:
(1) I have deleted the 2nd and 4th columns and want to use formula for columns 1, 2, and 3 (right now it is using columns 1, 3, 5.
(2) I also want to be able to use the formula if I want 4 columns instead of 3. In this case use columns 1, 2, 3, and 4.

I am really struggling trying to modify formula. I have attached revised spreadsheet. I really appreciate your help. Thanks

=INDIRECT("data!A"&INT(((ROW()-1)*Z-2)/Y)*Y+ROW()+(COLUMN()-1)/X*35)

I have renamed the 3 constants you need to change:
X=1 , with your new example, now blank columns
X=2, original formula, one space column
X=3, if you for some reason wants 2 space columns

Y=35 and Z=1 , If you only want 2 columns of keywords
Y=70 and Z=2, original formula, 3 columns of keywords
Y=105 and Z=3, if you deside to have 4 columns of keyword.

I hope this is correct.... there is always some trail end error with these kind of formulas...