Sorting Data without Blank Cell

[ainansari]

My Data is in a single column with blank rows....such as
A
B

C

D
E

Now I want to copy the same data and paste with other location without Blank Rows...and it should be look like
A
B
C
D
E

Please help without the use of Visual Basic. It should be done with formula.

Regard,

Arif Ansari

 

[Zakkaroo]

Assuming the data with blank cells is in column A and the place you want to put the data without blank cells is in column B ...

=IF(ROWS(B$1:B1)>COUNTA(A:A),"",INDEX(A:A,SMALL(IF(A$1:A$1000<>"",ROW(A$1:A$1000)),ROWS(B$1:B1))))

NOTE: This is an array formula, you must confirm it by hitting Ctrl + Shift + Enter, not just Enter. You can then fill it down from B1 to Bx

 

[ainansari]

Thanks Zakkaroo........

But I simply don't want to copy the same and apply I want to learn could you please explain....ROWS COUNTA INDEX SMALL ETC. used......

Thanks

Assuming the data with blank cells is in column A and the place you want to put the data without blank cells is in column B ...

=IF(ROWS(B$1:B1)>COUNTA(A:A),"",INDEX(A:A,SMALL(IF(A$1:A$1000<>"",ROW(A$1:A$1000)),ROWS(B$1:B1))))

NOTE: This is an array formula, you must confirm it by hitting Ctrl + Shift + Enter, not just Enter. You can then fill it down from B1 to Bx