Remove the empty rows and display in another sheet

[srsh911]

[TABLE="width: 1088"]
<colgroup><col span="15"><col></colgroup><tbody>[TR]
[TD="colspan: 16"]I have a report data like the below in sheet 1[/TD]
[/TR]
[TR]
[TD]149502[/TD]
[TD="colspan: 15"] [/TD]
[/TR]
[TR]
[TD]149534[/TD]
[/TR]
[TR]
[TD]151229[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]151235[/TD]
[/TR]
[TR]
[TD]151267[/TD]
[/TR]
[TR]
[TD]151301[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]151337[/TD]
[/TR]
[TR]
[TD]155142[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]155351[/TD]
[/TR]
[TR]
[TD]165620[/TD]
[/TR]
[TR]
[TD]169847[/TD]
[/TR]
[TR]
[TD]278985[/TD]
[/TR]
[TR]
[TD]112121[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 16"]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.[/TD]
[/TR]
[TR]
[TD]149502[/TD]
[TD="colspan: 15"] [/TD]
[/TR]
[TR]
[TD]149534[/TD]
[/TR]
[TR]
[TD]151229[/TD]
[/TR]
[TR]
[TD]151235[/TD]
[/TR]
[TR]
[TD]151267[/TD]
[/TR]
[TR]
[TD]151301[/TD]
[/TR]
[TR]
[TD]151337[/TD]
[/TR]
[TR]
[TD]155142[/TD]
[/TR]
[TR]
[TD]155351[/TD]
[/TR]
[TR]
[TD]165620[/TD]
[/TR]
[TR]
[TD]169847[/TD]
[/TR]
[TR]
[TD]278985[/TD]
[/TR]
[TR]
[TD]112121[/TD]
[/TR]
</tbody>[/TABLE]

 

[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