Stacking Multiple Columns ignoring 0 and blanks

[Shiftywicket]

Hi, I was looking at an old topic from 2014 and I am trying to extend an array formula to stack data from 9 columns ignoring 0 and blanks.
My workings are in below screenshot;

and sample data is as below;

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	17	0	0	0	0	0	0	0
2	232563								Entity 1	Entity 2	Entity 3	Entity 4	Entity 5	Entity 6	Entity 7	Entity 8		All SAP #
3	539115								232563.539115.B									IF($AQ$1>=ROWS(BH$3:BH3),INDEX(AQ:AQ,SMALL(IF($AQ$3:$AQ$500<>0,ROW($AQ$3:$AQ$500)),ROWS(BH$3:BH3))),IF($AQ$1+$AR$1>=ROWS(BH$3:BH3),INDEX(AR:AR,SMALL(IF($AR$3:$AR$500<>0,ROW($AR$3:$AR$500)),ROWS(BH$3:BH3)-$AQ$1))),IF($AQ$1+$AR$1+$AS$1>=ROWS(BH$3:BH3),INDEX(AS:AS,SMALL(IF($As$3:$As$500<>0,ROW($As$3:$As$500)),ROWS(BH$3:BH3)-($AQ$1+$AR$1))),IF($AQ$1+$AR$1+$AS$1+$AT$1>=ROWS(BH$3:BH3),INDEX(AT:AT,SMALL(IF($AT$3:$AT$500<>0,ROW($AT$3:$AT$500)),ROWS(BH$3:BH3)-($AQ$1+$AR$1+$AS$1)))),IF($AQ$1+$AR$1+$AS$1+$AT$1+$AU$1>=ROWS(BH$3:BH3),INDEX(AU:AU,SMALL(IF($AU$3:$AU$500<>0,ROW($AU$3:$AU$500)),ROWS(BH$3:BH3)-($AQ$1+$AR$1+$AS$1+$AT$1))),IF($AQ$1+$AR$1+$AS$1+$AT$1+$AU$1+$AV$1>=ROWS(BH$3:BH3),INDEX(AV:AV,SMALL(IF($AV$3:$AV$500<>0,ROW($AV$3:$AV$500)),ROWS(BH$3:BH3)-($AQ$1+$AR$1+$AS$1+$AT$1+$AU$1)))),IF($AQ$1+$AR$1+$AS$1+$AT$1+$AU$1+$AV$1+$AW$1>=ROWS(BH$3:BH3),INDEX(AW:AW,SMALL(IF($AW$3:$AW$500<>0,ROW($AW$3:$AW$500)),ROWS(BH$3:BH3)-($AQ$1+$AR$1+$AS$1+$AT$1+$AU$1+$AV$1))),IF($AQ$1+$AR$1+$AS$1+$AT$1+$AU$1+$AV$1+$AW$1+$AX$1>=ROWS(BH$3:BH3),INDEX(AX:AX,SMALL(IF($AX$3:$AX$500<>0,ROW($AX$3:$AX$500)),ROWS(BH$3:BH3)-($AQ$1+$AR$1+$AS$1+$AT$1+$AU$1+$AV$1+$AW$1)))),""))
4	539115								232563.539115.B
5	539115								232563.539115.B
6	539115								232563.539115.B
7	539115								232563.539115.B
8	559437								232563.559437.B
9	559437								232563.559437.B
10	559437								232563.559437.B
11	559437								232563.559437.B
12	559437								232563.559437.B
13	586977								232563.586977.B
14	586977								232563.586977.B
15	586977								232563.586977.B
16	586977								232563.586977.B
17	597069								232563.597069.B
18	597069								232563.597069.B
19	597069								232563.597069.B
Sheet1

The blank cells are either blank or 0s which have been told to show as blank
I am trying to stack A3:A500, B3:B500 Until H3:H500 starting in R3

The formula in R3 is rather monstrous (run as an array);

=IF($AQ$1>=ROWS(BH$3:BH3),INDEX(AQ:AQ,SMALL(IF($AQ$3:$AQ$500<>0,ROW($AQ$3:$AQ$500)),ROWS(BH$3:BH3))),IF($AQ$1+$AR$1>=ROWS(BH$3:BH3),INDEX(AR:AR,SMALL(IF($AR$3:$AR$500<>0,ROW($AR$3:$AR$500)),ROWS(BH$3:BH3)-$AQ$1))),IF($AQ$1+$AR$1+$AS$1>=ROWS(BH$3:BH3),INDEX(AS:AS,SMALL(IF($As$3:$As$500<>0,ROW($As$3:$As$500)),ROWS(BH$3:BH3)-($AQ$1+$AR$1))),IF($AQ$1+$AR$1+$AS$1+$AT$1>=ROWS(BH$3:BH3),INDEX(AT:AT,SMALL(IF($AT$3:$AT$500<>0,ROW($AT$3:$AT$500)),ROWS(BH$3:BH3)-($AQ$1+$AR$1+$AS$1)))),IF($AQ$1+$AR$1+$AS$1+$AT$1+$AU$1>=ROWS(BH$3:BH3),INDEX(AU:AU,SMALL(IF($AU$3:$AU$500<>0,ROW($AU$3:$AU$500)),ROWS(BH$3:BH3)-($AQ$1+$AR$1+$AS$1+$AT$1))),IF($AQ$1+$AR$1+$AS$1+$AT$1+$AU$1+$AV$1>=ROWS(BH$3:BH3),INDEX(AV:AV,SMALL(IF($AV$3:$AV$500<>0,ROW($AV$3:$AV$500)),ROWS(BH$3:BH3)-($AQ$1+$AR$1+$AS$1+$AT$1+$AU$1)))),IF($AQ$1+$AR$1+$AS$1+$AT$1+$AU$1+$AV$1+$AW$1>=ROWS(BH$3:BH3),INDEX(AW:AW,SMALL(IF($AW$3:$AW$500<>0,ROW($AW$3:$AW$500)),ROWS(BH$3:BH3)-($AQ$1+$AR$1+$AS$1+$AT$1+$AU$1+$AV$1))),IF($AQ$1+$AR$1+$AS$1+$AT$1+$AU$1+$AV$1+$AW$1+$AX$1>=ROWS(BH$3:BH3),INDEX(AX:AX,SMALL(IF($AX$3:$AX$500<>0,ROW($AX$3:$AX$500)),ROWS(BH$3:BH3)-($AQ$1+$AR$1+$AS$1+$AT$1+$AU$1+$AV$1+$AW$1)))),""))

I think I may have some punctuation out of place. Any assistance greatly appreciated. The legacy topic is here: Stacking multiple columns into one column without including 0 or Blanks

Many thanks in advance for your help

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Shiftywicket]

 

[Shiftywicket]

And thank you very much for your response

 

[Peter_SSs]

Re post #3:
What about above that?

 

[Shiftywicket]

Aha!

 

[Fluff]

Thanks for that, how about

Fluff.xlsm
	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH
1	17	1	2	0	0	0	0	0
2	232563	123							Entity 1	Entity 2	Entity 3	Entity 4	Entity 5	Entity 6	Entity 7	Entity 8
3	539115	abc							232563.539115.B									539115
4	539115								232563.539115.B									539115
5	539115		0						232563.539115.B									539115
6	539115		6789						232563.539115.B									539115
7	539115								232563.539115.B									539115
8	559437								232563.559437.B									559437
9	559437								232563.559437.B									559437
10	559437								232563.559437.B									559437
11	559437								232563.559437.B									559437
12	559437								232563.559437.B									559437
13	586977								232563.586977.B									586977
14	586977								232563.586977.B									586977
15	586977								232563.586977.B									586977
16	586977								232563.586977.B									586977
17	597069								232563.597069.B									597069
18	597069								232563.597069.B									597069
19	597069								232563.597069.B									597069
20																		abc
21																		6789
22
Data

Cell Formulas
Range	Formula
BH3:BH21	BH3	=LET(x,TOCOL(AQ3:AX500,1,1),FILTER(x,(x<>0)*(x<>"")))
Dynamic array formulas.

Please don't forget to update your profile to show you are using 365.

 

[Shiftywicket]

That is a beautiful, elegant and generally sexy solution to my problem. I was not familiar enough with the LET and TOCOL functions to put it together so I am very grateful.
I shall update my profile immediately. For you
Have an awesome day! You have made tomorrow a lot easier for me

 

[Fluff]

Glad we could help & thanks for the feedback.