Joining VLookups with line breaks but ignoring blanks

[Andy Roberts]

I have a spreadsheet of addresses e.g.

Name Address1 Address2 Address3 Postcode

In a different workbook I performing a lookup on the Name cell and in a
merged celled below I'm bringing in the address information as a joined up
lookup with character returns so I get

Name
Address1
Address2
Address3
Postcode

I am using the following equation...

=TRIM(VLOOKUP(C9,'[B.xlsx]Sheet1'!$A$8:$Q$50,2,0)&CHAR(10)&VLOOKUP(C9,'[B.xlsx]Sheet1'!$A$8:$Q$50,3,0)&CHAR(10)&VLOOKUP(C9,'[B.xlsx]Sheet1'!$A$8:$Q$50,4,0)&CHAR(10)&VLOOKUP(C9,'[B.xlsx]Sheet1'!$A$8:$Q$50,5,0))

The problem occurs when I have an address which doesn't have any data in the
Address3 column so my result looks like...

Name
Address1
Address2

Postcode

How can I amend my formula to ignore the blank cell so that I always get
each cell content under each other with no blank row.

e.g.
Name
Address1
Address2
Address3
Postcode

or

Name
Address1
Address2
Postcode

Regards

Andy
Office 2010
Win XP Pro SP3

 

[Ron Rosenfeld]

I have a spreadsheet of addresses e.g.

Name Address1 Address2 Address3 Postcode

In a different workbook I performing a lookup on the Name cell and in a
merged celled below I'm bringing in the address information as a joined up
lookup with character returns so I get

Name
Address1
Address2
Address3
Postcode

I am using the following equation...

=TRIM(VLOOKUP(C9,'[B.xlsx]Sheet1'!$A$8:$Q$50,2,0)&CHAR(10)&VLOOKUP(C9,'[B.xlsx]Sheet1'!$A$8:$Q$50,3,0)&CHAR(10)&VLOOKUP(C9,'[B.xlsx]Sheet1'!$A$8:$Q$50,4,0)&CHAR(10)&VLOOKUP(C9,'[B.xlsx]Sheet1'!$A$8:$Q$50,5,0))

The problem occurs when I have an address which doesn't have any data in the
Address3 column so my result looks like...

Name
Address1
Address2

Postcode

How can I amend my formula to ignore the blank cell so that I always get
each cell content under each other with no blank row.

e.g.
Name
Address1
Address2
Address3
Postcode

or

Name
Address1
Address2
Postcode

Regards

Andy
Office 2010
Win XP Pro SP3

It would be simpler to use a User Defined Function but, to do this in code, I would suggest a few nested SUBSTITUTE functions:

=SUBSTITUTE(SUBSTITUTE(your_formula,REPT(CHAR(10),3),CHAR(10)),REPT(CHAR(10),2),CHAR(10))

How many you need to nest will depend on how many blank lines in the address block you need to account for. The above will take care of one or two blank lines.

 

[Andy Roberts]

Thanks Ron, that works perfectly.

I have a spreadsheet of addresses e.g.

Name Address1 Address2 Address3 Postcode

In a different workbook I performing a lookup on the Name cell and in a
merged celled below I'm bringing in the address information as a joined up
lookup with character returns so I get

Name
Address1
Address2
Address3
Postcode

I am using the following equation...

=TRIM(VLOOKUP(C9,'[B.xlsx]Sheet1'!$A$8:$Q$50,2,0)&CHAR(10)&VLOOKUP(C9,'[B.xlsx]Sheet1'!$A$8:$Q$50,3,0)&CHAR(10)&VLOOKUP(C9,'[B.xlsx]Sheet1'!$A$8:$Q$50,4,0)&CHAR(10)&VLOOKUP(C9,'[B.xlsx]Sheet1'!$A$8:$Q$50,5,0))

The problem occurs when I have an address which doesn't have any data in
the
Address3 column so my result looks like...

Name
Address1
Address2

Postcode

How can I amend my formula to ignore the blank cell so that I always get
each cell content under each other with no blank row.

e.g.
Name
Address1
Address2
Address3
Postcode

or

Name
Address1
Address2
Postcode

Regards

Andy
Office 2010
Win XP Pro SP3

It would be simpler to use a User Defined Function but, to do this in
code, I would suggest a few nested SUBSTITUTE functions:

=SUBSTITUTE(SUBSTITUTE(your_formula,REPT(CHAR(10),3),CHAR(10)),REPT(CHAR(10),2),CHAR(10))

How many you need to nest will depend on how many blank lines in the
address block you need to account for. The above will take care of one or
two blank lines.

 

[Ron Rosenfeld]

Thanks Ron, that works perfectly.

Glad to help. Thanks for the feedback. (I should have written, "to do this with worksheet functions", rather than "to do this in code").