View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bj bj is offline
external usenet poster
 
Posts: 1,397
Default convert one column into many columns

sorry about that, when I was testing it, I had to change to *9 I must have
copied the wrong cell to paste into the suggestion

"Ajay" wrote:

Dear Sebation,
When I tried your script, it could change only the record with the
delimiter, i.e every 9th record.

Dear bj,
This trick worked...I changed the formula to *9 instead of 7 and it worked
like charm.
Thank you very much both of you!

best regards,
Ajay


"bj" wrote:

If I am reading your question right you want to move A1:A9 to A1:I9
and A10:A18 to A2:I2
etc.
or whatever columns you want to use

if this is correct
for data in column A
in B1 enter
=INDIRECT("A"&((ROW()-1)*7+COLUMN()-1))
copy over to column H and as far down as needed
select B:H copy and paste special values
delete column A

you will have to adjust the row and column adders for other than A1 as your
first data point.
be sure you try this on a copy before you put it in the final document.

"Ajay" wrote:

Hi,
I have a column which contains data that shd go into 9columns.

can anybody tell me how to convert this single column into 9 columns? I have
a delimiter at every 9th record.

thanks,
Ajay