# Sorting Data without Blank Cell

#### ainansari

##### New Member
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

Regard,

Arif Ansari

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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

Last edited:
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

Replies
7
Views
220
Replies
3
Views
95
Replies
4
Views
79
Replies
4
Views
270
Replies
3
Views
183

1,206,759
Messages
6,074,771
Members
446,086
Latest member
daywi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back