Transpose information in Excel

[newfrontiers]

Hello. I have 5 columns of data (Columns A through E) and approximately 1000 rows.

I need to transpose the information as follows:

ORIGINAL DATA
A B C D E
1 ETR 10 FRONT 30 BASE
2 GEF 12.2 BASE 87 FRONT

Would be changed to:

A B C
1 ETR 10 FRONT
2 GEF 12.2 BASE
3 ETR 30 BASE
4 GEF 87 FRONT

Is there an easy way to do this or a macro/program someone can direct me to to accomplish this?

Thank you very much.

 

[SkipVought]

Hi,

I'd use MS Query. faq68-5829

First, your table ought to have on heading row in Row 1, liek this...
[tt]
Key Val1 Bas1 Val2 Bas2
[/tt]
The use a UNION ALL query like this, assuming that your data is on Sheet1...
[tt]
Select Key, Val1, Bas1
From [Sheet1$]
UNION ALL
Select Key, Val2, Bas2
From [Sheet1$]
[/tt]


Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[yogia]

Hi newfrontiers:

The suggestion by Skip to use MS Query is the way to go. However,

for a formula based approach, with your original data in columns A:E starting with row 1 down,

for the new data to be formulated in columns G:I, use the following formula in cell G1 ...

=INDEX($A:$E,ROW()/2+VLOOKUP(MOD(ROW(),4),{0,0;1,0.5;2,1;3,-0.5},2),IF(COLUMNS($A:A)=1,1,INT((MOD(ROW(),4+(MOD(ROW(),2)=0)))/3)*2+COLUMNS($A:A)))

and copy this to G1:I whatever

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com