If cell is 0, move to next row

[jlea94]

I'm listing 50 rows x 8 columns of cells (defined 'allhazards')

However each cell in myhazards is referencing other sheets and contain 0's where there is no text to be referenced. When I list myhazards in a single column using this formula:

=INDEX(allhazards,1+INT((ROW($A1)-1)/COLUMNS(allhazards)),MOD(ROW($A1)-1+COLUMNS(allhazards),COLUMNS(allhazards))+1)

How do I implement this:

if cell in 'allhazards' is 0, do not reference this, move to next row
...then reference next row's columns until cell is 0, then move to next row
...keep doing this until there are no rows left to be referenced

eg. if 'allhazards' contained these cells (2 rows x 8 columns):
hello how are 0 0 0 0 0
good 0 0 0 0 0 0 0

It should produce this:
hello
how
are
good

but not this:
hello
how
are
0
0
0
0
0
good
0
0
0
0
0
0
0


Cross-posted here: http://www.excelforum.com/excel-programming-vba-macros/1103028-if-cell-is-0-index-next-row.html

 

[MarcelBeug]

My suggestion would be to keep the results with zeroes as helper column and the final result with the following array formula, confirm with Ctrl+Shift+Enter, not just Enter, assuming the interim results are in $A$4:$A$19 (adjust as applicable), in B4:

=IFERROR(INDEX($A$4:$A$19,SMALL(IF($A$4:$A$19<>0,ROW($A$4:$A$19)-ROW($A$4)+1),ROWS(A$4:A4))),"")

and copy down.