CODEWITHSUNDEEP
excelexcel-formula
Renato
Renato

Reputation: 556

Take a column of formula cells that return empty or a result, get only cells with a result

Given a range of cells A1 to A1000 containing formulas returning either values or empty, I need to place formulas on cells B1 to B1000 so that B1 will contain the first value returned from a formula in A1:A1000, B2 will contain the second, and so on, until there are no values left to be returned.

This means the end result would be a simple list containing all values returned in the referenced range, in order and without any empty rows between one and another.

I thought of using the below formula to get the first cell that returned something, and then on the next formula checking against the previous result, but no dice, as it returns an error:

=VLOOKUP("*"; $A$1:$A$1000; 1; FALSE)

Upvotes: 2

Views: 101

Answers (3)

user4039065
user4039065

Reputation:

Use,

=INDEX(A:A, AGGREGATE(15, 6, ROW($1:$999)/((ROW($1:$999)>1)*(A$1:A$999<>"")), ROW(1:1)))

Fill down as necessary. If your data starts in row 1, then get rid of (ROW($1:$999)>1).

no_spaces

Upvotes: 2

Werrf
Werrf

Reputation: 1148

A formula isn't going to give you a good answer. If you just need a list of non-blank values, you might want to think about using the autofilter to just filter out blanks, or using a sort to put cells with values at the top. If you do need a distinct list, your best bet would probably be a short macro like this:

Sub RemoveBlanks()
    Const csS As String = "A"
    Const csT As String = "B"

    Dim rngCell As Range
    Dim rngSource As Range
    Dim rngTarget As Range
    Dim lngLast As Long

    lngLast = Range(csS & Rows.Count).End(xlUp).Row

    Set rngSource = Range(csS & 1, csS & lngLast)
    Set rngTarget = Range(csT)
    For Each rngCell In rngSource
        If rngCell.Value <> "" Then
            rngTarget.Value = rngCell.Value
            Set rngTarget = rngTarget.Offset(1, 0)
        End If
    Next rngCell
End Sub

The values for csS and csT can be changed to reflect your actual source and target columns.

Upvotes: -1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

You can avoid array formulas with a helper column:

In C1 enter

=IF(A1="",0,1)

In C2 enter:

=IF(A2="","",MAX($C$1:C1)+1)

and copy down. Then in B1 enter:

=INDEX(A:A,MATCH(ROW(),C:C,0))

and copy down:

enter image description here

Note that column C "marks" the rows containing values to be retrieved and column B retrieves the information.

Upvotes: 2

Related Questions