If cell is 0, move to next index

[jlea94]

I have defined my data (set of hazards set out 20 rows x 4 columns) as 'myhazards'

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

I drag this down and A1 changes to A2, to A3 etc. to get the column of elements

I'm listing them from this:
a1 a2 0 a4
b1 0 b3 0

to this:

a1
a2
0
a4
b1
0
b3
0

However I don't want the cells to be listed if their value is 0 (zero) i.e. I want them to be like this:

a1
a2
a4
b1
b3

How do I modify my function line to get it to do this? Cheers!

 

[BlokeMan]

Cross-post;If cell is 0, INDEX next row

 

[Joe4]

Also duplicated: http://www.mrexcel.com/forum/excel-questions/881293-if-cell-0-move-next-row.html#post4269109

Please do not post the same question multiple times. All clarifications, directly-related follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: Forum Rules).

Regarding Cross-Posting:
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #10 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: Excelguru Help Site - A message to forum cross posters).