Stacking multiple columns into one column without including 0 or Blanks

[RocketAnt]

Column A	Column B	Column C
Program X	Program Y	Stack
12345	54321	12345
23451	43215	23451
34512	32154	34512
45123	21543	45123
FMPU	0	FMPU
0	0	54321
		43215
0	0	32154
0		21543

<tbody>
</tbody>

Is there a way, formula or VBA, to stack column A and B and have it appear in column C, like the above? I'm trying to avoid stacking with blanks and 0s. My worksheet has numbers, letters, and a combo of both. I also want the stacking to be next, and on the same worksheet as A and B. Thanks for the help!

 

[Marcelo Branco]

Hi and welcome to Mr Excel

Maybe something like this

	A​	B​	C​	D​	E​	F​
1​	Program X​	Program Y​	Stack​		Count PrgX​	Count PrgY​
2​	12345​	54321​	12345​		5​	4​
3​	23451​	43215​	23451​
4​	34512​	32154​	34512​
5​	45123​	21543​	45123​
6​	FMPU​	0​	FMPU​
7​	0​	0​	54321​
8​			43215​
9​	0​	0​	32154​
10​	0​		21543​
11​

<TBODY>
</TBODY>

Formula in E2
=SUM(COUNTIF(A2:A100,{">0";"?*"}))

Formula in F2
=SUM(COUNTIF(B2:B100,{">0";"?*"}))

Array formula in C2 copied down
=IF($E$2>=ROWS(C$2:C2),INDEX(A:A,SMALL(IF($A$2:$A$100<>0,ROW($A$2:$A$100)),ROWS(C$2:C2))),IF($E$2+$F$2>=ROWS(C$2:C2),INDEX(B:B,SMALL(IF($B$2:$B$100<>0,ROW($B$2:$B$100)),ROWS(C$2:C2)-$E$2)),""))

confirmed with Ctrl+Shift+Enter simultaneously
(hold down both Ctrl and Shift keys and hit Enter)

Hope this helps

M.

 

[RocketAnt]

Forgot the Array Formula part! THANKS!

 

[Marcelo Branco]

Forgot the Array Formula part! THANKS!

You are welcome and thanks for the feedback.

M.

 

[archvanarl]

Hi. How about combining four columns with the same condition (excluding blanks and zeros)? Anyone?

 

[sandy666]

maybe try Table.ToColumns then filter by blanks and zeroes
PowerQuery required