Formula to transpose every second element from row into column

[smide]

Hello.

All cells in Sheet1 row3 contains some text or values (70 elements in total) but I need to copy/transpose contents only from every second cell from Sheet1 row3 into Sheet4 column B, starting from B4 cell (B4:B38, first 35 elements).

Than again from Sheet1 row3 same action again, but now for the last 35 cells/elements into Sheet4 column H (H4:H38).

I would like formula(s), not macro.

Example.

Sheet1 (row 3, columns A-BR)

	A	B	C	D	E	F	G	H
1
2
3	shop	mrexcel	pattern	home	4.76	salary	storm	....

<tbody>
</tbody>

Sheet4 (after copy/transpose)

	B
1
2
3
4	mrexcel
5	home
6	salary
7	.....

<tbody>
</tbody>

Sheet1 (row3, columns BS-EJ)

	BS	BT	BU	BV	BW	BX	BY	...
1
2
3	city	value	airport	7.28	budget	2.15	eclipse	...

<tbody>
</tbody>

Sheet4

	H
1
2
3
4	value
5	7.28
6	2.15
7	.....

<tbody>
</tbody>

 

[Rick Rothstein]

Here is one way to do it. Put this formula in cell H4 on Sheet4 and copy it down to Row 74...

=IF(INDEX(Sheet1!A$3:BR$3,1,2*ROWS($1:1))="","",INDEX(Sheet1!A$3:BR$3,1,2*ROWS($1:1)))

 

[netuser]

Here is another way to do it using Offset, put formula in B4 and copy downwards


Excel 2010 32 bit

	A	B	C	D	E	F	G
3	shop	mrexcel	pattern	home	4.76	salary	storm
4		mrexcel
5		home
6		salary

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Feuil33​

Worksheet Formulas Cell Formula B4 =OFFSET($B$3,0,(ROW()-4)*2,1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Rick Rothstein]

Here is another way to do it using Offset, put formula in B4 and copy downwards


Excel 2010 32 bit

	A	B	C	D	E	F	G
3	shop	mrexcel	pattern	home	4.76	salary	storm
4		mrexcel
5		home
6		salary

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Feuil33​

Worksheet Formulas Cell Formula B4 =OFFSET($B$3,0,(ROW()-4)*2,1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The Offset function is Volatile and if there are blank cells, your formula reports 0 instead of "". I would suggest using the INDEX function as I proposed in my last message as the INDEX function is not Volatile (my formula is also designed to suppress the 0's for blank cells).

 

[netuser]

You are expert Rick so I will recommend using your solution But to over come 0 in my case you just have to add if statement

=if(OFFSET($B$3,0,(ROW()-4)*2,1)=0,"",OFFSET($B$3,0,(ROW()-4)*2,1))