Transposing data

[Guest]

Quick question for something that may or may not be able to be done in excel.

I get data in the following format in an excel workbook:
a b c d e f
1 yada yada yada yada
2 data1
3 data2
4 yada yada yada yada
5 data1
6 data2

Would like 2 have a second page where it would look like
a b c d e f
1 yada yada yada yada data1 data2
2 yada yada yada yada data1 data2

I have tried just doing an =a2 and a3 into e1 and f1 and then autofilling
but can never get it done correctly. I thought this was something pretty
common so figured I would ask in here if there was a quick and easy solution.
If anyone can help it would be much appreciated.

Smish

 

[Guest]

It's really not a pure 'transpose' operation (for which there's an option
under Paste Special). But I think you can get this with an offset. In e1:
=offset(a1,1,0) and in f1: =offset(a1,2,0). Autofill.
If you want to delete the old partial rows: copy, paste special values.
Then autofilter to select and delete the rows that have blanks in column B.
HTH. --Bruce