excelexcel-formulaexcel-2013

Remove blank rows from a column

I am in search of a formula that can help me remove blank rows from a column. I tried different formulas, but with no luck.

Apreciate the help.

enter image description here

I cant use sort because the imput data changes. I tried different formulas found online, but didnt work.

Solution

  • Here is a way for those using Excel 2013:

    enter image description here

    =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$15)/(A$2:A$15<>""),ROWS(B$1:B1))),"")

    Or MS365 Users can use TOCOL() or FILTER()

    enter image description here

    =TOCOL(A2:A15,1)

    Or,

    =FILTER(A2:A15,A2:A15<>"")