Listing non-blank cells in Excel based on adjacent column(s)

Solution 1:

This will work for your example an can easily be adapted if you have headers, more columns, or more rows.

=IFERROR(INDEX($A$1:$A$4,SMALL(IF(ISBLANK($B$1:$B$4),"",ROW($B$1:$B$4)-ROW($C$1)+1),ROW(C1)-ROW($C$1)+1)),"")

Enter the formula in C1 and hold Ctrl+Shift then press Enter. Expand the formula to C4 to get the full results for your example.

Headers or columns may now be inserted in the example and this will still work. To handle more rows, change the range $A$1:$A$4 and $B$1:$B$4 accordingly

See also Microsoft Support article Finding the nth Value That Meets a Condition.

Solution 2:

Why not filter the list in place? Filter the list based on column B for all rows where B is not blank e.g.

Then if you need it in column C, just copy and paste it. Remember that once you filter some cells are hidden so copy/paste might not look correct until you clear the filter.