Sorting a text column while ignoring blank cells

Hello,

I need to sort a text column and ignore (or put at the end of the sorted column) all blank cells. I've been using this formula to sort so far (entered with Ctrl-Shift-Enter as an array formula):

=IF(ROWS($B$1:B1)<=COUNTA(NAMES);
INDEX(NAMES;MATCH( SMALL(COUNTIF(NAMES;"<"&NAMES);ROWS($B$1:B1)); COUNTIF(NAMES;"<"&NAMES); 0));
"")

(Please note I'm using semicolons instead of commas)

The above formula would sort a named range called NAMES in column A like this in column B:

Please Login or Register  to view this content.

But there's a problem if the named range has blank cells in the end, then the first name is repeated in the sorted column as many times as the blank cells, at the expense of the last ones that disappear, like this

Please Login or Register  to view this content.

How can I achieve the desired result which is the following ?

Please Login or Register  to view this content.

Thank you in advance!

Hi there.Καλημέρα Πατρίδα!

Assuming that you data are in column A, highlight column A and insert a new column. So now your data will be in column B, starting in B2.

So in A2 and copy down put this.

=COUNTIF($B$2:$B$21;"<="&B2)

This will be a helper and hidden column.

Now in C2 and copy down, use this one.

=IFERROR(VLOOKUP(ROW()-ROW($C$1);$A$2:$B$21;2;FALSE);"")

Comments?

welcome to the forum, mic2mic. here's another alternative:

Formula:

Please Login or Register  to view this content.

array formula too. here's the explanation:
http://www.get-digital-help.com/2009...mula-in-excel/

You are welcome and thanks for the feed back.

If you google.. for excel uniques..., it's 100% sure that will provide the suggested ny Ben site. It is 1 of the best!!