What you want to do is to concatenate the data excluding the #N/A errors.

You can do this easily with a VBA function. Are you open to doing that?