Transpose every 7-th cell value from horizontal to vertical

[strooman]

I'm struggling a little bit with transposing (see example below). Thought I had a ssimple approach but that was definitely not a good approach ;o)) I tried:
=TRANSPOSE(MOD(COLUMN(C1:AL1)-4;7)=0)
With Control+Shift+Enter
I also experimented with INDEX combined with MOD to skip the blanks and only get the numbers. No succes.

Bottom-line, I want to transpose the values in row 1 from horizontal to vertical in column A. As you can see there are always six empty cells that I want to skip when I go from horizontal to vertical. Any Ideas would be very welcome?

TourPool2023.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1			242							70							71							60							76							210
2	242
3	70
4	71
5	60
6	76
7	210
Kladblok

 

[lrobbo314]

How about this?

GDO
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1			242							70							71							60							76							210
2	242		242							70							71							60							76							210
3	70
4	71
5	60
6	76
7	210
Sheet7

Cell Formulas
Range	Formula
C2:AL2	C2	=IF(MOD(COLUMN()-3,7)=0,INDEX($A$2:$A$7,COUNT($B$2:B2)+1),"")

 

[strooman]

How about this?

=IF(MOD(COLUMN()-3,7)=0,INDEX($A$2:$A$7,COUNT($B$2:B2)+1),"")

Perhaps I explained myself badly. The original values, horizontal in row 1 (C1:AL1) have to be transposed to column A (A2:A7) vertical. Your formula just puts them in row 2 (C2:AL2) as far as i can see. That could be achieved with a simpel copy and paste but that is not what I'm looking for. Or am I missing something?

 

[lrobbo314]

I did it backwards.

GDO
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1			242							70							71							60							76							210
2	242	242
3	70	70
4	71	71
5	60	60
6	76	76
7	210	210
Sheet7

Cell Formulas
Range	Formula
B2:B7	B2	=INDEX($C$1:$AL$1,,AGGREGATE(15,6,COLUMN($C$1:$AL$1)/($C$1:$AL$1<>""),ROW(B1))-COLUMN(C1)+1)

 

[strooman]

Always looking for shorter formulas because the longer they get I lose track and it's more difficult to comprehend for me. I use a lot of F9 to understand what is going on. I fiddled around and came up with this formula which also seems to do the job.
FORMULA in A2 copy down:

=OFFSET($C$1,0,(ROWS($A$1:$A1)-1)*7)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1			242							70							71							60							76							210
2	242
3	70
4	71
5	60
6	76
7	210
8
9
10
11
Data

Cell Formulas
Range	Formula
A2:A7	A2	=OFFSET($C$1,0,(ROWS($A$1:$A1)-1)*7)

 

[Peter_SSs]

Always looking for shorter formulas because the longer they get I lose track and it's more difficult to comprehend for me.

I would be a little careful in choosing a formula simply because of its length. For example, the formula you have used in post #5 uses the volatile function OFFSET which may have a negative performance impact on your worksheet.
What about this equally simple, and non-volatile, alternative?

23 08 13.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1			242							70							71							60							76							210
2	242
3	70
4	71
5	60
6	76
7	210
Index

Cell Formulas
Range	Formula
A2:A7	A2	=INDEX(C$1:AL$1,7*ROWS(A$2:A2)-6)