Horizontal Data to Vertical Data

[EricBB]

my data table,

Act id Lab Car SF Mas
1 2 3 4 6
2 6 3 5 8
10 5 7 4 6

how can i make a FORMULA for the above table to be like this,

Actid
1 Lab 2
1 Car 3
1 SF 4
1 Mas 6

2 Lab 6
2 Car 3
2 SF 5
2 Mas 8

10 Lab 5
10 Car 7
10 SF 4
10 Mas 6

i know how to do it by copying and transpose, what i need is a formula....

pls. help

 

[EricBB]

what im looking is a formula to find the 3rd column. 1st & 2nd column is a
manual entry.

 

[Stefi]

On another sheet:
In 2nd column:
=INDEX(OFFSET(Sheet1!$B$1,0,0,4,4),1,MOD(ROW()-2,5)+1)
In 3rd column:
=INDEX(OFFSET(Sheet1!$B$1,0,0,4,4),INT((ROW()-1)/5)+2,MOD(ROW()-2,5)+1)

and fill them down!

Regards,
Stefi

„EricBB†ezt írta: