Automatic Transposing of data

**wilfordsy** · 06-03-2010, 02:04 AM

Hi everyone. I'm a new user and I'm having a hard time transposing some data in excel.

I found this thread (http://www.excelforum.com/excel-work...matically.html) on transposing data automatically but I have a different setup and I don't understand fully the codes in that thread.

basically I have 5 different topics with 40 items.

topic1 topic2 topic3 topic4 topic5
a1 b1 c1 d1 e1
a2 b2 c2 d2 e2
a3 b3 c3 d3 e3
a4 b4 c4 d4 e4
etc

So what I want to happen is transpose everything getting 2 from each topic.

a1
a2
b1
b2
c1
c2
d1
d2
e1
e2
a3
etc...

or at the very least (if its too hard) into something like this:

a1
b1
c1
d1
e1
a2
b2
c2
d2
e2

All help will be greatly appreciated. :-)

thanks a lot!

**arthurbr** · 06-03-2010, 03:10 AM

Hi and welcome to the board
several solutions are presented here

**wilfordsy** · 06-03-2010, 03:10 AM

Thanks a lot for the time guys. I found the solutions here:

http://www.ozgrid.com/forum/showthread.php?t=32501

Many thanks!

**wilfordsy** · 06-03-2010, 03:12 AM

Woah. We posted at the same exact time arthurbr.

Anyway, the link your provided is also a big help. I'm also brushing up on some basic excel coding.

Thanks a lot!

**DonkeyOte** · 06-03-2010, 03:19 AM

Late to the party... but going forward: always a good idea to post sample files in these cases.

For sake of demo. let's assume your pseudo table appears in A1:E5 with headers in row 1.

With header in G1 we can get the desired output listed in G2 onwards using:

=INDEX($A$2:$E$5,1+2*FLOOR((ROWS(G$2:G2)-1)/10,1)+MOD(ROWS(G$2:G2)-1,2),1+MOD(CEILING(ROWS(G$2:G2)/2,1)-1,5))
copied down

(or something along those lines)