# Sort non blank cells

#### eduzs

##### Well-known Member
I need to list the non blank cells in order they appear: (. = blank line/cell)

.
A
.
.
C
.
.
F

3 lines with:

A
C
F

Thanks

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
explaining better:

I have this:
 1 2 A 3 4 5 B

<tbody>
</tbody>

Need a formula to show this in an another area of the sheet:

 2 A 5 B

<tbody>
</tbody>

So the formula must get the number in column A when column B is not blank
In column B i will use VLOOKUP easy, but wich formula to use in Column A?

See if this gives what you want.

Formula in D2 is an array formula and must be entered with
Excel Workbook
ABCDE
1##
212A
32A5B
43
54
65B
7
CTRL-SHIFT-ENTER. Sheet3

[/B]

Just needed to change ISBLANK...=0 to \$F\$4:\$F\$17<>"" (Cause the cells have formulas =""), works great.

Thanks!

If you would prefer a non CTRL-SHIFT-ENTER formula and you are using Excel 2010 or later, an alternative is:

Excel Workbook
ABCDE
1
212A
32A5B
43
54
65B
7
Exclude Blanks

Thanks! I was also looking for a non-matrix formula.
Works great.

Replies
7
Views
174
Replies
9
Views
473
Replies
4
Views
555
Replies
5
Views
339
Replies
1
Views
195

1,221,521
Messages
6,160,302
Members
451,637
Latest member
hvp2262

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