CODEWITHSUNDEEP
arraysexcel
M.Doe
M.Doe

Reputation: 27

Create a list of non blank cell values from a range?

I have a spreadsheet like so:

Column A

Ducks
Frogs
<Blank>
Rabbits
<blank>
Horse

I want to be able to create a list of all non blank cells in a range (column A) like so:

Ducks
Frogs
Rabbits
Horse

I do not want blank values to be in my list.

Also, where there is a blank value found in the range, my formula should skip and list the next non blank cell in the range.

However at the moment i am getting 0

Here's my array formula, please can someone show me where i am going wrong?

=IFERROR(INDEX($F$16:$F$5000,IF($G$16:$G$5000<>"",ROW($1:$5000)),1),"")

Upvotes: 2

Views: 21641

Answers (3)

drekbour
drekbour

Reputation: 3081

I find the other solutions (and most search engine hits for this topic) kind of illegible :) Adapting to another/more-general usage is hard if it is not clear of itself.

No idea about performance but this implements a list-of-non-blanks from column A:

=INDEX( $A:$A, 2*ROW($A1)-COUNTIF($A$1:$A1,"<>") )

It works by counting how many blank cells are above and using that as the running offset needed to "look ahead".

Upvotes: 0

XOR LX
XOR LX

Reputation: 7742

IFERROR in this set-up can be very inefficient, particularly if the size of the dataset being queried is large; cf my reply here:

https://superuser.com/questions/812727/look-up-a-value-in-a-list-and-return-all-multiple-corresponding-values

Better to use a single cell in which to record the number of expected number of returns, and reference that cell in an initial IF clause within the main formulas. For example, if we use cell H16 for that purpose, we would put, in that cell:

=COUNTIF(G$16:G$5000,"<>")

The main array formula** is then:

=IF(ROWS($1:1)>H$16,"",INDEX(F:F,SMALL(IF(G$16:G$5000<>"",ROW(G$16:G$5000)),ROWS($1:1))))

and copied down until you get blanks.

For a unique list, change the formula in H16 to:

=SUM(IF(FREQUENCY(IF(G16:G5000<>"",MATCH(F16:F5000,F16:F5000‌​,0)),ROW(F16:F5000)-MIN(ROW(F16:F5000))+1),1))

and the main formula to:

IF(ROWS($1:1)>H$16,"",INDEX(F:F,SMALL(IF(FREQUENCY(IF(G$16:G$5000<>"",MATCH(F$16:F$5000,F$16:F$5000,0)),ROW(F$16:F$5000)-MIN(ROW(F$16:F$5000))+1),ROW(F$16:F$5000)),ROWS($1:1))))

Regards

Upvotes: 2

yd1
yd1

Reputation: 277

i suggest you consider using macro for this issue (its in the toolbar options). you can record an empty macro and paste the code i have appended. do note that macros overrides the ability to change back using history (ctrl+z). in the code the column is 16 and the last row is 5000, but you can change it.

Dim max As Integer
Dim colNum As Integer
Dim limit As Integer

max = 5000
colNum = 16

limit = max

For i = 1 To max
    If Cells(i, colNum) = "" Then
        Cells(i, colNum).Delete Shift:=xlUp
        If limit > 0 Then
            i = i - 1
            limit = limit - 1
        end if
    End If
Next i

Upvotes: 0

Related Questions