Excel Convert Multiple Rows into Multiple Columns

[james.y.so]

Hi All,

I need some help with converting my excel data from multiple rows to
multiple columns. Say i have a long list of serial numbers in a single
column, like this:

10000
10001
10002
10003
10004
10005
10006
.....
19999

How can i convert it to columns of data, like this?

10000 10005 10010 .....
10001 10006 10011 ....
10002 10007 10012 ....
10003 10008 10013 ....
10004 10009 10014 ....

I need each column having a specific numbers of rows, say 40, before
the data continues to the next column. Is there any functions other
than Macro programming?

Thanks in advance!

 

[Peo Sjoblom]

One way

assume the values start in A1 in Sheet1

insert a new sheet or use an empty sheet, in A1 of that sheet put


=INDEX(Sheet1!$A:$A,ROWS($A$1:A1)-1+COLUMNS($A$1:A1)*40-39)


copy across to IP1, then copy down 40 rows


then copy the whole lot while still selected, then do edit>paste special as
values (or else the workbook will be slow with all them formulas)


that will give you a table like





10000 10040 10080 10120 10160 10200 10240 10280 10320
10001 10041 10081 10121 10161 10201 10241 10281 10321
10002 10042 10082 10122 10162 10202 10242 10282 10322
10003 10043 10083 10123 10163 10203 10243 10283 10323
10004 10044 10084 10124 10164 10204 10244 10284 10324
10005 10045 10085 10125 10165 10205 10245 10285 10325
10006 10046 10086 10126 10166 10206 10246 10286 10326
10007 10047 10087 10127 10167 10207 10247 10287 10327
10008 10048 10088 10128 10168 10208 10248 10288 10328
10009 10049 10089 10129 10169 10209 10249 10289 10329
10010 10050 10090 10130 10170 10210 10250 10290 10330


but larger of course





--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)