Stacking multiple columns into one column without including 0 or Blanks

RocketAnt

New Member
Joined
Apr 1, 2014
Messages
15
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,344
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.
 

archvanarl

New Member
Joined
Nov 25, 2018
Messages
2
Hi. How about combining four columns with the same condition (excluding blanks and zeros)? Anyone?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,310
maybe try Table.ToColumns then filter by blanks and zeroes
PowerQuery required
 

Forum statistics

Threads
1,085,855
Messages
5,386,376
Members
401,996
Latest member
mg07p929

Some videos you may like

This Week's Hot Topics

Top