This is the follow-up question to (Excel formula to transpose horizontal values in vertical list). I have formula to display horizontally placed values in vertical table.
=INDEX($1:$1;(ROW(1:1)-1)*2+4)
=INDEX($1:$1;(ROW(2:2)-1)*2+4)
etc
The problem with current solution is that it is displaying (0) zero values if cells are empty. How I can modify this formula to hide all zero (0) values so it will give as a result ""
In one column I have numbers and in other one text. So solution should be suitable for both or optionally two different approaches I can input to two different columns.
P.S. I do not want to hide zero values by Excel options, because I will use values further on and need them to be =""
If you are retrieving text then just append a zero-length string.
=INDEX($1:$1;(ROW(1:1)-1)*2+4)&""
If you are retrieving something, it won't change the result. If there is nothing to return (blank) then nothing shows in the cell.
I don't recommend doing this when retrieving numbers or dates as it will change them to text.