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!

 

[StephenCrump]

When you are saying I could use 10 in this example because there is only less than 10 results?

With 10, the formula can accommodate a maximum of 10 rows.

Here's 12 rows, showing how the formula (using 10) starts to break:

	A	B	C	D	E	F	G	H	I
1
2	1	AA			FF
3	2	BB			GG
4	3		DD
5	4	CC
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12			EE
14
15		AA
16		BB
17		CC
18		DD
19		FF
20		GG
21		GG
22
23		Stack
24		by column					Explain
25		11	FALSE	FALSE	41		1	11	AA
26		12	FALSE	FALSE	42		2	12	BB
27		FALSE	23	FALSE	FALSE		3	14	CC
28		14	FALSE	FALSE	FALSE		4	23	DD
29		FALSE	FALSE	FALSE	FALSE		5	41	FF
30		FALSE	FALSE	FALSE	FALSE		6	42	GG
31		FALSE	FALSE	FALSE	FALSE		7	42	GG
32		FALSE	FALSE	FALSE	FALSE
33		FALSE	FALSE	FALSE	FALSE
34		FALSE	FALSE	FALSE	FALSE
35		FALSE	FALSE	FALSE	FALSE
36		FALSE	FALSE	42	FALSE
Sheet1

Cell Formulas
Range	Formula
B15:B21	B15	=IFERROR(INDEX(MyData,MOD(SMALL(IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+10*(1+COLUMN(MyData)-MIN(COLUMN(MyData)))),ROWS(B$15:B15)),10),INT(SMALL(IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+10*(1+COLUMN(MyData)-MIN(COLUMN(MyData)))),ROWS(B$15:B15))/10)),"")
B25:E36	B25	=IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+10*(1+COLUMN(MyData)-MIN(COLUMN(MyData))))
H25:H31	H25	=SMALL(B$25#,G25)
I25:I31	I25	=INDEX(MyData,MOD(H25,10),INT(H25/10))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyData	=Sheet3!$B$2:$E$13	B25, I25:I31, B15:B21

 

[ceecee88]

Does the order matter? If not, this is somewhat less 'horrible' than Stephen's and should also work in 2010.

22 07 05.xlsm
	A	B	C	D	E
1
2		AA			FF
3		BB			GG
4			DD
5		CC		EE
6
7
8		AA
9		FF
10		BB
11		GG
12		DD
13		CC
14		EE
15
16
To Column

Cell Formulas
Range	Formula
B8:B16	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),"")

May I ask what is the 10^6 and R000000C000000? If I understand it I'll be able to adjust accordingly in the future. Thank you!

 

[ceecee88]

With 10, the formula can accommodate a maximum of 10 rows.

Here's 12 rows, showing how the formula (using 10) starts to break:

	A	B	C	D	E	F	G	H	I
1
2	1	AA			FF
3	2	BB			GG
4	3		DD
5	4	CC
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12			EE
14
15		AA
16		BB
17		CC
18		DD
19		FF
20		GG
21		GG
22
23		Stack
24		by column					Explain
25		11	FALSE	FALSE	41		1	11	AA
26		12	FALSE	FALSE	42		2	12	BB
27		FALSE	23	FALSE	FALSE		3	14	CC
28		14	FALSE	FALSE	FALSE		4	23	DD
29		FALSE	FALSE	FALSE	FALSE		5	41	FF
30		FALSE	FALSE	FALSE	FALSE		6	42	GG
31		FALSE	FALSE	FALSE	FALSE		7	42	GG
32		FALSE	FALSE	FALSE	FALSE
33		FALSE	FALSE	FALSE	FALSE
34		FALSE	FALSE	FALSE	FALSE
35		FALSE	FALSE	FALSE	FALSE
36		FALSE	FALSE	42	FALSE
Sheet1

Cell Formulas
Range	Formula
B15:B21	B15	=IFERROR(INDEX(MyData,MOD(SMALL(IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+10*(1+COLUMN(MyData)-MIN(COLUMN(MyData)))),ROWS(B$15:B15)),10),INT(SMALL(IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+10*(1+COLUMN(MyData)-MIN(COLUMN(MyData)))),ROWS(B$15:B15))/10)),"")
B25:E36	B25	=IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+10*(1+COLUMN(MyData)-MIN(COLUMN(MyData))))
H25:H31	H25	=SMALL(B$25#,G25)
I25:I31	I25	=INDEX(MyData,MOD(H25,10),INT(H25/10))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyData	=Sheet3!$B$2:$E$13	B25, I25:I31, B15:B21

I see, thank you so much! I really appreciate your time.

 

[Peter_SSs]

May I ask what is the 10^6 and R000000C000000?

10^6 multiplies the row number by 1 million so that it is separated from the column number when that is added.
R000000C000000 is the R1C1 notation to be used in the INDIRECT function, allowing for the fact that the row numbers were multiplied by 1 million.
See here for more on INDIRECT function

The formula is not an easy one to explain.

 

[ceecee88]

May I ask what is the 10^6 and R000000C000000? If I understand it I'll be able to adjust accordingly in the future. Thank you!

I tried it and got #VALUE!, my actual data has 60 columns and 150 rows, don't know if that has anything to do with the error or not. Thank you!

 

[Peter_SSs]

I tried it and got #VALUE!, my actual data has 60 columns and 150 rows, don't know if that has anything to do with the error or not. Thank you!

What is the actual range that your data is in?
What is the adjusted formula that you used?
What cell is the first formula in?

 

[ceecee88]

What is the actual range that your data is in?
What is the adjusted formula that you used?
What cell is the first formula in?

I test it if the data and the output is in the same sheet then it work perfectly! But in this case, they are in a different sheet may be that's why it showing 0? My apology for not having this information previously.

 

[Peter_SSs]

If they are on different sheets then you need to include the sheet name in the references and the INDIRECT function. Here is a small example.

ceecee88.xlsm
	A	B	C	D	E	F
1
2		AA			FF
3		BB			GG
4			DD
5		CC		EE
6
Sheet 3

ceecee88.xlsm
	A
1
2	AA
3	FF
4	BB
5	GG
6	DD
7	CC
8	EE
9
10
Sheet 2

Cell Formulas
Range	Formula
A2:A10	A2	=IFERROR(INDIRECT("'Sheet 3'!"&TEXT(AGGREGATE(15,6,(ROW('Sheet 3'!B$2:E$5)*10^6+COLUMN('Sheet 3'!B$2:E$5))/('Sheet 3'!B$2:E$5<>""),ROWS(A$2:A2)),"R000000C000000"),0),"")

 

[ceecee88]

If they are on different sheets then you need to include the sheet name in the references and the INDIRECT function. Here is a small example.

ceecee88.xlsm
	A	B	C	D	E	F
1
2		AA			FF
3		BB			GG
4			DD
5		CC		EE
6
Sheet 3

ceecee88.xlsm
	A
1
2	AA
3	FF
4	BB
5	GG
6	DD
7	CC
8	EE
9
10
Sheet 2

Cell Formulas
Range	Formula
A2:A10	A2	=IFERROR(INDIRECT("'Sheet 3'!"&TEXT(AGGREGATE(15,6,(ROW('Sheet 3'!B$2:E$5)*10^6+COLUMN('Sheet 3'!B$2:E$5))/('Sheet 3'!B$2:E$5<>""),ROWS(A$2:A2)),"R000000C000000"),0),"")

It work now! but it's going to the right and then down. How do I make it going down first then next column? Thank you!

 

[StephenCrump]

Does the order matter? If not, this is somewhat less 'horrible' than Stephen's and should also work in 2010.

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"),),"")