Collapse address fields for account statement

Question

I have a sheet with 7 fields:

Name 1
Name 2
Street Address 1
Street Address 2
City
State 
Zip

There is a multitude of possibilities with the above between company name, c/o name, Unit# or no Unit#, etc. Another sheet is an account statement which needs to look clean. What formulas can I put in the account statement address fields to automatically remove empty fields with no information?

I've tried a bunch of if and isblank formulas, but its tough to wrap my head around it - maybe theres another way?

Answer 1

try:

=QUERY({'9102-1000 Doe'!C3:C4;
  '9102-1000 Doe'!D3:D4;
  '9102-1000 Doe'!D5&", "&'9102-1000 Doe'!D6&" "&'9102-1000 Doe'!D7}, 
 "where Col1 is not null", 0)