none
How to get the previous non-empty cells in the column RRS feed

  • Question

  • Hi,

    The bold values below in Row 2, 6, 7 and 10 are fixed.

    I would like to know what is the formula to find the previous non-empty cell in the column. e.g, for Row 4, it should have the value b, Row 8 should have the value d as show below.

    Please advice. Thanks in advance.

    
    Monday, October 31, 2016 7:17 PM

Answers

  • For numbers, you'd use

    =LOOKUP(9.99999999999999E+307,A$1:A3)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by wkpli Thursday, November 3, 2016 2:35 PM
    Tuesday, November 1, 2016 3:43 PM

All replies

  • In A4, enter the formula

    =LOOKUP(REPT("z",255),A$1:A3)

    You can copy A4 to other empty cells.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, October 31, 2016 7:27 PM
  • Thanks Hans, it works when it is in text format

    Actually, what if it is a number? 

    Thanks

    • Edited by wkpli Tuesday, November 1, 2016 1:22 PM
    Tuesday, November 1, 2016 12:52 PM
  • For numbers, you'd use

    =LOOKUP(9.99999999999999E+307,A$1:A3)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by wkpli Thursday, November 3, 2016 2:35 PM
    Tuesday, November 1, 2016 3:43 PM
  • Hi wkpli,

    Thank Hans for the solid suggestion.

    For custom function to achieve your requirement, I suggest you try to achieve your own function.

    //defination
    Function getNonEmpty(r As Range)
        Dim row As Integer
        row = r.Rows.Count
        For i = row To 1 Step -1
            If r.CurrentRegion.Cells(i, r.Column) <> "" Then
                getNonEmpty = r.CurrentRegion.Cells(i, r.Column).Value
                Exit For
            End If
        Next i
    End Function
    //use
    =getNonEmpty(A1:A4)

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 2, 2016 5:54 AM