Transpose and Alphabatize

I have dynamic titles in row A, listed in no order and with blank cells between all the titles. On another sheet I want the titles listed in column 1, alphabetically and without gaps.

I have gotten very close by using the COUNTIF function, but have had trouble looking up the results. Sorry if this is confusing, and thank you in advance.

-Joe

=IF(ISNUMBER(SMALL(IF(Sheet1!$A$1:$A$15="","",COUNTIF(Sheet1!$A$1:$A$15,"<"&Sheet1!$A$1:$A$15)),ROW(Sheet1!A1))),INDEX(Sheet1!$A$1:$A$15,MATCH(SMALL(IF(Sheet1!$A$1:$A$15="","",COUNTIF(Sheet1!$A$1:$A$15,"<"&Sheet1!$A$1:$A$15)),ROW(Sheet1!A1)),IF(Sheet1!$A$1:$A$15="","",COUNTIF(Sheet1!$A$1:$A$15,"<"&Sheet1!$A$1:$A$15)),0)),"")

adjust sheet name and ranges to suit..

then confirmed with CTRL+SHIFT+ENTER instead of just ENTER and copy down.

Depending on your range size, this could be quite slow...

you can split the formula and use a helper column...

I.e. in the original sheet add formula next to column A...

=IF(A1="","",COUNTIF($A$1:$A$100,"<"&A1)) and copy down... this finds sort 

order.

then in sheet2, use formula:

=IF(ISNUMBER(SMALL(Sheet1!$B$1:$B$100,ROW(A1))),INDEX(Sheet1!$A$1:$A$100,MATCH(SMALL(Sheet1!$B$1:$B$100,ROW(A1)),Sheet1!$B$1:$B$100,0)),"")

which is to be confirmed with CTRL+SHIFT+ENTER after adjusting sheet name and ranges...then copied down.

Attached workbook shows both methods.
.
.