Copy specific cells from rows to columns

[KH12]

Many thanks for anyone who understands and can help me with a solution.
For a sheet with hundreds of rows I need to copy specific cells from each a row to cells in a column, followed by the same cells of all next rows.

The below image ilustrates what I'm trying to accomplish.

Any ideas?

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7	Header 8	Header 9
2		Value B2			A	B	C	D	E			Value B2	A
3		Value B3			F	G	H	I	J			Value B2	B
4		Value B4			K	L	M	N	O			Value B2	C
5		Value B5			P	Q	R	S	T			Value B2	D
6		Value B6			U	V	W	X	Y			Value B2	E
7		Value B7			Z	AA	AB	AC	AD			Value B3	F
8		Value B8			AE	AF	AG	AH	AI			Value B3	G
9		Value B9			AJ	AK	AL	AM	AN			Value B3	H
10												Value B3	I
11												Value B3	J
12												Value B4	K
13												Value B4	L
14												Value B4	M
15												Value B4	N
16												Value B4	O
17												Value B5	P
18												Value B5	Q
19												Value B5	R
20												Value B5	S
21												Value B5	T
22												Value B6	U
23												Value B6	V
24												Value B6	W
25												Value B6	X
26												Value B6	Y
27												Value B7	Z
28												Value B7	AA
29												Value B7	AB
30												Value B7	AC
31												Value B7	AD
32												Value B8	AE
33												Value B8	AF
34												Value B8	AG
35												Value B8	AH
36												Value B8	AI
37												Value B9	AJ
38												Value B9	AK
39												Value B9	AL
40												Value B9	AM
41												Value B9	AN
Data

Cell Formulas
Range	Formula
L2:L41	L2	=TOCOL(IF(E2:I9="",a,B2:B9),3)
M2:M41	M2	=TOCOL(E2:I9,3)
Dynamic array formulas.

 

[KH12]

Many thanks!
This works great, although that first colomn (L2:L41 range) does not seem to work on new sheet when referring to a range on another sheet.
For the M2:M41 range formula thos works good. Replaced the ,3 with a 0 though to include blank cells.

 

[KH12]

Many thanks!
This works great, although that first colomn (L2:L41 range) does not seem to work on new sheet when referring to a range on another sheet.
For the M2:M41 range formula thos works good. Replaced the ,3 with a 0 though to include blank cells.

I needed add that it throws a #SPILL and later on a #CALC error when using this on a new sheet, referring to a range on another sheet.
Using the exact same formula in the same sheet works fine.

 

[Fluff]

How did you modify the two formulae to look at another sheet?

 

[KH12]

How did you modify the two formulae to look at another sheet?

I used:

=TOCOL('CMDB input 1'!Q4:X105;0)
and
=TOCOL(IF('CMDB input 1'!Q4:X105="";a;'CMDB input 1'!F4:F105);3)

The first one works, the second one give the #SPILL error.
If I use the excat same formule in CMDB input 1 sheet it works fine.
If I use ;0 in the end is shows #Name and many more results i'd expect.
Please note that I do have blank cells in array Q4:X105, hance the ;0 in the first formula.

I dont understand the "";a;' int he formula so not sure whats happends there to correct this.

 

[Fluff]

You will get a spill error if there are non-empty cells below the formula, so you need to clear those cells.

Please note that I do have blank cells in array Q4:X105, hance the ;0 in the first formula.

In that case please provide a realistic sample of your data along with expected results.