Find values on an a range of columns and rows and copy them to a column

[vladi305]

I have groups of data on a range of columns and row. Some of those are blank. The solution will be to copy into a column the cells that contain a value.
Notice the picture attached. The groups are the one left wIth a background filled and the column is the one on the right

 

[Fluff]

Are the "blank" cells totally empty, or do they contain a formula?

 

[vladi305]

Are the "blank" cells totally empty, or do they contain a formula?

They contain formulas

 

[Fluff]

Ok, how about

=TOCOL(IF(D3:M7="",x,D3:M7),3)

 

[vladi305]

Ok, how about

=TOCOL(IF(D3:M7="",x,D3:M7),3)

I'll try it when I get to the office. I'm in a mobile now and im getting a #CALC error

 

[Fluff]

Ok

 

[vladi305]

Ok, how about

=TOCOL(IF(D3:M7="",x,D3:M7),3)

I tried this and still getting the #calc! Error
I removed the if condition and it works fine but it returns the blank cells
=TOCOL(D3:M7,3)
On the If condition what does the x stand for? Is that a variable?

 

[shaowu459]

try this one:

=TOCOL(D3:M7/LEN(D3:M7),3)

工作簿1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3		1		1
4		5										5
5		3					3		4
6		4		2								6	8
7		2											8
8		6
9		8
10		8
11
12
Sheet1

Cell Formulas
Range	Formula
B3:B10	B3	=TOCOL(D3:M7/LEN(D3:M7),3)
Dynamic array formulas.

 

[shaowu459]

I tried this and still getting the #calc! Error
I removed the if condition and it works fine but it returns the blank cells
=TOCOL(D3:M7,3)
On the If condition what does the x stand for? Is that a variable?

the x will be recognized as an #name error, and TOCOL will ignore these errors when the second parameter is 3 or 2. you can use \ to instead x.

 

[vladi305]

the x will be recognized as an #name error, and TOCOL will ignore these errors when the second parameter is 3 or 2. you can use \ to instead x.

Now I'm getting the #name error