Insert filtered values into a table without spill error

Question

Column A below contains numbers 1-9 some of which are merged with empty cells.

I wish to filter out the blanks and normally would use the formula =FILTER(A3:A15,A3:A15<>"").

This work great normally, but Because this produces a dynamic array when entered into a table this causes a spill error.

Can anyone think of way clean way around this? i.e. without hiding columns. Normally I would use power query but this is only an example question of a much larger spreadsheet which I don't want to alter.

Using Power query would convert this to a table before outputting the actual table desired. Also, I do have multiple tables in this workbook and unless I can specifically turn off the 'Autofill' feature for a specific table this is also not feasible.

If you don't think its possible that's equally as useful. Thanks

Answer 1

You have to use a formula which will only output a single result and not an array.

For example

C2: =IFERROR(INDEX(FILTER($A$1:$A$13,$A$1:$A$13<>""),ROWS($1:1)),"")

and fill down.

Unfortunately, I don't know of any way, using formulas, to size the table so it is just the right size to encompass the entries in Column A.