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!

 

[portews]

Cheap and dirty way, if your formula works otherwise... put in

=IF(your formula=0,"",your formula)

.

 

[StephenCrump]

I need it for older version ....

Much easier in 365, of course.

Here's one horrible formula that should work in 2010:

	A	B	C	D	E
1
2		AA			FF
3		BB			GG
4			DD
5		CC		EE
6
7
8		AA
9		BB
10		CC
11		DD
12		EE
13		FF
14		GG
15
16
17
18
Sheet1

Cell Formulas
Range	Formula
B8:B17	B8	=IFERROR(INDEX(MyData,MOD(SMALL(IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+1000*(1+COLUMN(MyData)-MIN(COLUMN(MyData)))),ROWS(B$8:B8)),1000),INT(SMALL(IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+1000*(1+COLUMN(MyData)-MIN(COLUMN(MyData)))),ROWS(B$8:B8))/1000)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
MyData	=Sheet3!$B$2:$E$5	B8:B17

 

[ceecee88]

Cheap and dirty way, if your formula works otherwise... put in

=IF(your formula=0,"",your formula)

.

Thank you, but I kinda need to row to adjust up when there is no value (blank), the mentioned formula will only make the 0 disappear but still having those excessive row. But thanks though ^^

 

[ceecee88]

Much easier in 365, of course.

Here's one horrible formula that should work in 2010:

	A	B	C	D	E
1
2		AA			FF
3		BB			GG
4			DD
5		CC		EE
6
7
8		AA
9		BB
10		CC
11		DD
12		EE
13		FF
14		GG
15
16
17
18
Sheet1

Cell Formulas
Range	Formula
B8:B17	B8	=IFERROR(INDEX(MyData,MOD(SMALL(IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+1000*(1+COLUMN(MyData)-MIN(COLUMN(MyData)))),ROWS(B$8:B8)),1000),INT(SMALL(IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+1000*(1+COLUMN(MyData)-MIN(COLUMN(MyData)))),ROWS(B$8:B8))/1000)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
MyData	=Sheet3!$B$2:$E$5	B8:B17

 

[ceecee88]

Thank you so much let me try!

It work perfectly! Thank you!!! Can you help explain a bit more what is 1000 does in this formula?

Thanks again!

 

[StephenCrump]

The first step in the formula is to create an array that I can sort using the SMALL function. 1,000 is a slightly arbitrary number - it just needs to be sufficiently large to get the sort in the right order. In this simple case, we would use 10 instead of 1,000, so by using 1,000 I have included some wiggle room in case your actual sample is much bigger.

If you're using Excel 2010, you won't see the formulae in B17 and B24 spill like this, but it will illustrate what's happening.

	A	B	C	D	E
1
2		AA			FF
3		BB			GG
4			DD
5		CC		EE
6
7		AA
8		BB
9		CC
10		DD
11		EE
12		FF
13		GG
14
15		Stack
16		by column
17		11	FALSE	FALSE	41
18		12	FALSE	FALSE	42
19		FALSE	23	FALSE	FALSE
20		14	FALSE	34	FALSE
21
22		Stack
23		by row
24		11	FALSE	FALSE	14
25		21	FALSE	FALSE	24
26		FALSE	32	FALSE	FALSE
27		41	FALSE	43	FALSE
28
Sheet1

Cell Formulas
Range	Formula
B7:B13	B7	=IFERROR(INDEX(MyData,MOD(SMALL(IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+1000*(1+COLUMN(MyData)-MIN(COLUMN(MyData)))),ROWS(B$7:B7)),1000),INT(SMALL(IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+1000*(1+COLUMN(MyData)-MIN(COLUMN(MyData)))),ROWS(B$7:B7))/1000)),"")
B17:E20	B17	=IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+10*(1+COLUMN(MyData)-MIN(COLUMN(MyData))))
B24:E27	B24	=IF(LEN(MyData),10*(1+ROW(MyData)-MIN(ROW(MyData)))+1+COLUMN(MyData)-MIN(COLUMN(MyData)))
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyData	=Sheet3!$B$2:$E$5	B24, B17, B7:B13

 

[ceecee88]

The first step in the formula is to create an array that I can sort using the SMALL function. 1,000 is a slightly arbitrary number - it just needs to be sufficiently large to get the sort in the right order. In this simple case, we would use 10 instead of 1,000, so by using 1,000 I have included some wiggle room in case your actual sample is much bigger.

If you're using Excel 2010, you won't see the formulae in B17 and B24 spill like this, but it will illustrate what's happening.

	A	B	C	D	E
1
2		AA			FF
3		BB			GG
4			DD
5		CC		EE
6
7		AA
8		BB
9		CC
10		DD
11		EE
12		FF
13		GG
14
15		Stack
16		by column
17		11	FALSE	FALSE	41
18		12	FALSE	FALSE	42
19		FALSE	23	FALSE	FALSE
20		14	FALSE	34	FALSE
21
22		Stack
23		by row
24		11	FALSE	FALSE	14
25		21	FALSE	FALSE	24
26		FALSE	32	FALSE	FALSE
27		41	FALSE	43	FALSE
28
Sheet1

Cell Formulas
Range	Formula
B7:B13	B7	=IFERROR(INDEX(MyData,MOD(SMALL(IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+1000*(1+COLUMN(MyData)-MIN(COLUMN(MyData)))),ROWS(B$7:B7)),1000),INT(SMALL(IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+1000*(1+COLUMN(MyData)-MIN(COLUMN(MyData)))),ROWS(B$7:B7))/1000)),"")
B17:E20	B17	=IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+10*(1+COLUMN(MyData)-MIN(COLUMN(MyData))))
B24:E27	B24	=IF(LEN(MyData),10*(1+ROW(MyData)-MIN(ROW(MyData)))+1+COLUMN(MyData)-MIN(COLUMN(MyData)))
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyData	=Sheet3!$B$2:$E$5	B24, B17, B7:B13

Thank you so much, I'll study for it. Probably need a bit of research and a time to digest. ^^

 

[ceecee88]

The first step in the formula is to create an array that I can sort using the SMALL function. 1,000 is a slightly arbitrary number - it just needs to be sufficiently large to get the sort in the right order. In this simple case, we would use 10 instead of 1,000, so by using 1,000 I have included some wiggle room in case your actual sample is much bigger.

If you're using Excel 2010, you won't see the formulae in B17 and B24 spill like this, but it will illustrate what's happening.

	A	B	C	D	E
1
2		AA			FF
3		BB			GG
4			DD
5		CC		EE
6
7		AA
8		BB
9		CC
10		DD
11		EE
12		FF
13		GG
14
15		Stack
16		by column
17		11	FALSE	FALSE	41
18		12	FALSE	FALSE	42
19		FALSE	23	FALSE	FALSE
20		14	FALSE	34	FALSE
21
22		Stack
23		by row
24		11	FALSE	FALSE	14
25		21	FALSE	FALSE	24
26		FALSE	32	FALSE	FALSE
27		41	FALSE	43	FALSE
28
Sheet1

Cell Formulas
Range	Formula
B7:B13	B7	=IFERROR(INDEX(MyData,MOD(SMALL(IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+1000*(1+COLUMN(MyData)-MIN(COLUMN(MyData)))),ROWS(B$7:B7)),1000),INT(SMALL(IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+1000*(1+COLUMN(MyData)-MIN(COLUMN(MyData)))),ROWS(B$7:B7))/1000)),"")
B17:E20	B17	=IF(LEN(MyData),1+ROW(MyData)-MIN(ROW(MyData))+10*(1+COLUMN(MyData)-MIN(COLUMN(MyData))))
B24:E27	B24	=IF(LEN(MyData),10*(1+ROW(MyData)-MIN(ROW(MyData)))+1+COLUMN(MyData)-MIN(COLUMN(MyData)))
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyData	=Sheet3!$B$2:$E$5	B24, B17, B7:B13

One quick questions, when you are saying I could use 10 in this example because there is only less than 10 results? the number using is the estimate of number of result? Am I understand that correctly?

 

[Peter_SSs]

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