Excel: Reference non-zero cells

Question

I'm trying to list 50 rows x 8 columns of cells (defined 'allhazards') into one column

However each cell in myhazards is referencing other sheets and contain 0's where there is no text to be referenced.

When I list the data in 'allhazards' 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)

(then drag down the column to get all of the cells from 'allhazards')

How do I implement this:

if cell in 'allhazards' is 0, do not reference this cell, 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 (eg. 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 when dragging down the formula:

hello
how
are
good

but not this:

hello
how
are
0
0
0
0
0
good
0
0
0
0
0
0
0

Are you OK with a user defined function created in VBA that you can use on the worksheet? — Excel Hero

Excel Hero Excel Hero · Accepted Answer · 2015-09-08T02:04:54

I created a UDF for your situation. Please place the following procedure in a standard code module.

Public Function MATRIX2VECTOR(r As Range)
    Dim i&, j&, k&, v, m, o
    v = r
    ReDim o(1 To Application.Caller.Rows.Count, 1 To 1)
    For i = 1 To UBound(v, 1)
        For j = 1 To UBound(v, 2)
            m = v(i, j)
            If Len(m) Then
                If m <> 0 Then
                    k = k + 1
                    o(k, 1) = v(i, j)
                End If
            End If
        Next
    Next
    For k = k + 1 To UBound(o): o(k, 1) = "": Next
    MATRIX2VECTOR = o
End Function

Now you can call it in a formula from the worksheet just like any of the built-in functions.

1

Select a vertical range of cells tall enough to accommodate the transposed data.

2

Click in the Formula Bar at the top of Excel.

3

Enter this formula:

=MATRIX2VECTOR(allhazards)

4

This is an array formula and must be confirmed with Ctrl+Shift+Enter.

Excel: Reference non-zero cells

2 Answers

1

2

3

4