How to assemble a column with only filled cells from a column with empty and filled cells?

Question

excel

Please tell me the formula. Or how it can be done. There is a column1 with data, some cells are filled, some are not. You need to create a column2 (next to or on another sheet) with data from column1, only without gaps, all cells in a row are filled.

Answer 1

On Sheet1, in column A you have the initial data.
Put the cursor on B1, insert the formula.
Press Ctrl+Shift+Enter to activate the array formula.

=ЕСЛИОШИБКА(ИНДЕКС(Лист1!A$1:A$99;НАИМЕНЬШИЙ(ЕСЛИ(Лист1!A$1:A$99<>"";СТРОКА(Лист1!A$1:A$99));СТРОКА(B1)));"")

Drag the selected cell B1 to the required length along the column.

Answer 2

It is possible still so to make: You filter a column. remove empty cells through the filter. copy and paste on a new sheet.