Remove the empty rows and display in another sheet

[srsh911]

I have a report data like the below in sheet 1
149502
149534
151229
151235
151267
151301
151337
155142
155351
165620
169847
278985
112121
And I would like to remove the empty rows between the cells and display in sheet 2 as below, the empty space may vary in all the reports. I don’t want it it VBA but in formulas.
149502
149534
151229
151235
151267
151301
151337
155142
155351
165620
169847
278985
112121

<colgroup><col span="15"><col></colgroup><tbody>
</tbody>

 

[Comfy]

Is the order important?

Just highlight the column and sort it.

 

[srsh911]

Is the order important?

Just highlight the column and sort it.

YES the order is needed. i need to delete the empty row and place the other row one by one .

 

[wideboydixon]

Or you could do something like this:

Book1
	A	B
1	149502	149502
2	149534	149534
3	151229	151229
4		151235
5	151235	151267
6	151267	151301
7	151301	151337
8		155142
9	151337	155351
10	155142	165620
11		169847
12		278985
13		112121
14
15
16
17
18
19	155351
20	165620
21	169847
22	278985
23	112121
Sheet1

Cell Formulas
Range	Formula
B1	{=IFERROR(INDEX($A:$A,SMALL(IF($A:$A<>"",ROW($A:$A)),ROWS($B$1:$B1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Not good on performance though: It would be better to replace $A:$A with the actual range of values (e.g. $A1:$A23 in this case).

WBD