Remove blank or zeros when transposing data

Hi

I have an address field of 5 columns in one worksheet that I am attempting to transpose into another sheet so that the address reads as normal ( in rows rather than columns). I have used VLOOKUP to transpose the data.

The problem is that not always are all 5 data columns used and so I end up with blank cells in the transposed data like this:

Original Data Sheet:
A B _ _ E

Transposed Address:
A
B
_
_

E



Is there a way (without VB) to ignore the blank cells during the vlookup so I end up with ABE in the example above? I was thinking IF AND VLOOKUP ISBLANK something but cannot get my poor head around it

Thanks
Mat

Originally Posted by martindwilson

try copy paste special values then filter on blanks then delete blank rows

Thanks Martin.

I need it updated automatically though so don't want to go for a copy/paste operation - it needs to be a formula ideally or VB if I cannot do the formula.