If function BUT with empty cells in between

[pedexe90]

Hello everyone,

I am looking for a formulate to incorporate the if function to another function that would avoid leaving gaps (empty cells) in between.

So I have data from lets say A1:A50

I want non zero values to be populated in column B without gaps in between.

Sorry if this is too easy and thanks for your help.

 

[Scott T]

Excel 2010
	A	B
1	7	7
2	0	8
3	8	7
4	7	9
5	9	1
6	1	2
7	2	3
8	3	4
9	4	5
10	5	6
11	6	8
12	0	9
13	8	55
14	9	66
15	55	12
16	66	99
17	12	7
18	99	44
19	7	7
20	44	77
21	7	7
22	77	8
23	7	6
24	8	1
25	6	6
26	1	1
27	6	6
28	1	7
29	6	7
30	7	7
31	7	66
32	7	7
33	66	7
34	7	7
35	7	99
36	7	7
37	99	7
38	7	7
39	7	78
40	7	7
41	78	6
42	7	7
43	6	7
44	7	4
45	0	7
46	7	4
47	4	7
48	7
49	4
50	7
Sheet4

Cell Formulas
Range	Formula
B1	{=IF(ROWS(B$1:B1)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B1))))}
B2	{=IF(ROWS(B$1:B2)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B2))))}
B3	{=IF(ROWS(B$1:B3)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B3))))}
B4	{=IF(ROWS(B$1:B4)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B4))))}
B5	{=IF(ROWS(B$1:B5)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B5))))}
B6	{=IF(ROWS(B$1:B6)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B6))))}
B7	{=IF(ROWS(B$1:B7)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B7))))}
B8	{=IF(ROWS(B$1:B8)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B8))))}
B9	{=IF(ROWS(B$1:B9)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B9))))}
B10	{=IF(ROWS(B$1:B10)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B10))))}
B11	{=IF(ROWS(B$1:B11)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B11))))}
B12	{=IF(ROWS(B$1:B12)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B12))))}
B13	{=IF(ROWS(B$1:B13)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B13))))}
B14	{=IF(ROWS(B$1:B14)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B14))))}
B15	{=IF(ROWS(B$1:B15)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B15))))}
B16	{=IF(ROWS(B$1:B16)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B16))))}
B17	{=IF(ROWS(B$1:B17)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B17))))}
B18	{=IF(ROWS(B$1:B18)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B18))))}
B19	{=IF(ROWS(B$1:B19)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B19))))}
B20	{=IF(ROWS(B$1:B20)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B20))))}
B21	{=IF(ROWS(B$1:B21)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B21))))}
B22	{=IF(ROWS(B$1:B22)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B22))))}
B23	{=IF(ROWS(B$1:B23)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B23))))}
B24	{=IF(ROWS(B$1:B24)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B24))))}
B25	{=IF(ROWS(B$1:B25)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B25))))}
B26	{=IF(ROWS(B$1:B26)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B26))))}
B27	{=IF(ROWS(B$1:B27)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B27))))}
B28	{=IF(ROWS(B$1:B28)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B28))))}
B29	{=IF(ROWS(B$1:B29)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B29))))}
B30	{=IF(ROWS(B$1:B30)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B30))))}
B31	{=IF(ROWS(B$1:B31)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B31))))}
B32	{=IF(ROWS(B$1:B32)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B32))))}
B33	{=IF(ROWS(B$1:B33)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B33))))}
B34	{=IF(ROWS(B$1:B34)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B34))))}
B35	{=IF(ROWS(B$1:B35)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B35))))}
B36	{=IF(ROWS(B$1:B36)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B36))))}
B37	{=IF(ROWS(B$1:B37)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B37))))}
B38	{=IF(ROWS(B$1:B38)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B38))))}
B39	{=IF(ROWS(B$1:B39)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B39))))}
B40	{=IF(ROWS(B$1:B40)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B40))))}
B41	{=IF(ROWS(B$1:B41)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B41))))}
B42	{=IF(ROWS(B$1:B42)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B42))))}
B43	{=IF(ROWS(B$1:B43)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B43))))}
B44	{=IF(ROWS(B$1:B44)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B44))))}
B45	{=IF(ROWS(B$1:B45)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B45))))}
B46	{=IF(ROWS(B$1:B46)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B46))))}
B47	{=IF(ROWS(B$1:B47)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B47))))}
B48	{=IF(ROWS(B$1:B48)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B48))))}
B49	{=IF(ROWS(B$1:B49)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B49))))}
B50	{=IF(ROWS(B$1:B50)>COUNTIF($A$1:$A$50,">0"),"",INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50>0,ROW($A$1:$A$50)-ROW($A$1)+1),ROWS(B$1:B50))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[pedexe90]

Thanks a lot.