Stacking multiple columns into one column without including 0 or Blanks

[RocketAnt]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[/TR]
[TR]
[TD]Program X
[/TD]
[TD]Program Y
[/TD]
[TD]Stack
[/TD]
[/TR]
[TR]
[TD]12345
[/TD]
[TD]54321
[/TD]
[TD]12345
[/TD]
[/TR]
[TR]
[TD]23451
[/TD]
[TD]43215
[/TD]
[TD]23451
[/TD]
[/TR]
[TR]
[TD]34512
[/TD]
[TD]32154
[/TD]
[TD]34512
[/TD]
[/TR]
[TR]
[TD]45123
[/TD]
[TD]21543
[/TD]
[TD]45123
[/TD]
[/TR]
[TR]
[TD]FMPU
[/TD]
[TD]0
[/TD]
[TD]FMPU
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]54321
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]43215
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]32154
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD][/TD]
[TD]21543
[/TD]
[/TR]
</tbody>[/TABLE]

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


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Program X​

[/TD]
[TD]

Program Y​

[/TD]
[TD]

Stack​

[/TD]
[TD][/TD]
[TD]

Count PrgX​

[/TD]
[TD]

Count PrgY​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

12345​

[/TD]
[TD]

54321​

[/TD]
[TD]

12345​

[/TD]
[TD][/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

23451​

[/TD]
[TD]

43215​

[/TD]
[TD]

23451​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

34512​

[/TD]
[TD]

32154​

[/TD]
[TD]

34512​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

45123​

[/TD]
[TD]

21543​

[/TD]
[TD]

45123​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

FMPU​

[/TD]
[TD]

0​

[/TD]
[TD]

FMPU​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

54321​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

43215​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

32154​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]

21543​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


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