Stacked multiple columns into one column ignoring blank and 0

[ceecee88]

Hi, this what I have got so far, I get it to stacked but it won't ignore blank (it is showing 0), I need it for older version, any suggestion please?

Thank you!

 

[ceecee88]

Very nice!

But I can't resist the slightly more horrible version that stacks by column ...

	A	B	C	D	E
1
2		AA			FF
3		BB			GG
4			DD
5		CC		EE
6
7
8		AA	AA
9		FF	BB
10		BB	CC
11		GG	DD
12		DD	EE
13		CC	FF
14		EE	GG
15
16
Sheet1

Cell Formulas
Range	Formula
B8:B15	B8	=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(B$2:E$5)*10^6+COLUMN(B$2:E$5))/(B$2:E$5<>""),ROWS(B$8:B8)),"R000000C000000"),0),"")
C8:C15	C8	=IFERROR(INDIRECT(TEXT(SUMPRODUCT(MID(TEXT(AGGREGATE(15,6,(COLUMN(B$2:E$5)*1000+ROW(B$2:E$5))/(B$2:E$5<>""),ROWS(C$8:C8)),"000000"),{4,1},3)*{1000,1}),"R000C000"),),"")

You are both are Superman! Thank you! Can I click resolved for both solution or I can only click one?

 

[Peter_SSs]

Can I click resolved for both solution or I can only click one?

You can only click one. If you are going with an INDIRECT solution and want the results by column then Stephen has got it in post #20.

 

[ceecee88]

You can only click one. If you are going with an INDIRECT solution and want the results by column then Stephen has got it in post #20.

Thank you again, I'm going with the indirect solution