"Merging" Columns while ignoring blank cells in Excel 2010 for Mac

[aTmb405]

I am having trouble moving data from one column (I am using this column as a helper column for data based on another sheet) into a different column on the same sheet, but without bringing along blank cells.
This is the situation:

A	B
	First Name
	Last Name
	Address

<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

What I've been trying to get:

A	B
First Name
Last Name
Address

<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

I need each different category (name, address, etc) to be in their own cell. Is there a way to do this without messing with the helper column's (column B's) function? The function is an IF function.

I have tried different array functions with no success.

 

[aTmb405]

After testing different array formulas, I keep receiving the message "Multi-cell array formulas are not allowed in tables."

Any thoughts?

 

[FDibbins]

1. Avoid merging cells if at all possible, they cause problems with some formulas
2. What formulas are you using in B?
3. What was the formula that didn't work?

 

[aTmb405]

1. Avoid merging cells if at all possible, they cause problems with some formulas
2. What formulas are you using in B?
3. What was the formula that didn't work?

The formula in B is =IF(Table1[@[Contacts]]>0, Sheet1!A4&" "&Sheet1!C4, "")

Formula that didn't work is

=IFERROR(INDEX($P$3:$P$502,SMALL(IF(ISTEXT($P$3:$P$502),ROW($A$1:$A$502)),ROW(A1))),"")