CODEWITHSUNDEEP
excelexcel-formula
preethi krishna
preethi krishna

Reputation: 5

Retrieve Last but one row value

I need to retrieve the last but one row value in column A/B(Value in Date)

Ex1: Row 3 : last column is AB. I need Row 3 Column AA value (Date) in Row 3 Column C.

Ex 2: Row 4:last column in AS: I need Column AR value (date) in ROw 4 column C.

Ex 3:Row 5:last column in BC: I need Column BB value (date) in ROw 5 column C.

there can b one or 2 blank values.

Please let me know if there are any excel formula to tackle this scenario.

Upvotes: 0

Views: 861

Answers (2)

zedfoxus
zedfoxus

Reputation: 37059

If you get inclined to VBA, you could try something like this:

' In C3 type =GetSecondLastColumnValue()
' This function will keep on checking values on column at a time to the right
' If 3 continuous empty cells are found, it will assume that there are no more
' columns with values after that. The last known cell with value will be identified
' and value of column just to its left will be plugged in to column C
Public Function GetSecondLastColumnValue()

    Dim Looper As Integer
    Looper = 1

    Const NoOfContinuousEmptyColumns As Integer = 3

    Dim m_Address As String, m_LastKnownColumnWithValue
    m_Address = Selection.Address
    m_LastKnownColumnWithValue = m_Address

    Do
        m_Address = Range(m_Address).Offset(0, 1).Address
        If Range(m_Address).Value = "" Then
            Looper = Looper + 1
            If Looper > NoOfContinuousEmptyColumns Then Exit Do
        Else
            Looper = 1
            m_LastKnownColumnWithValue = m_Address
        End If
    Loop

    m_LastKnownColumnWithValue = Range(m_LastKnownColumnWithValue).Offset(0, -1).Address
    GetSecondLastColumnValue = Range(m_LastKnownColumnWithValue).Value

End Function

Example:

    A   B   C   D   E   F
1  abc def      ab  cd
2  abc def      xy  zx  st

In C1, type =GetSecondLastColumnValue () and it will be populated with ab. In C2, type the same formula and it will be populated with zx

This is just an example of what you can do with VBA. Do NOT use this function in production. It is insanely greedy and slow. It's just an illustration.

This subroutine is much faster:

' This method keeps on going to far right till it reaches the end of the columns
' Then it comes back to the last column with value, and hops to the column to its left, and remembers its value
' That value goes in column C. It loops through all rows you specify in startrow and endrow
Sub PopulateColumnC()
    Dim StartRow As Integer, EndRow As Integer, RowLoop As Integer
    StartRow = 1
    EndRow = 3

    Dim m_Address As String

    For RowLoop = StartRow To EndRow
        Range("A" & RowLoop).Select
        m_Address = Selection.Address
        Do
            Selection.End(xlToRight).Select
            If m_Address = Selection.Address Then
                Selection.End(xlToLeft).Select
                Exit Do
            Else
                m_Address = Selection.Address
            End If
        Loop
        m_Address = Range(Selection.Address).Offset(0, -1).Address
        Range("C" & RowLoop).Value = Range(m_Address).Value
    Next

End Sub

Upvotes: 0

Dirk Reichel
Dirk Reichel

Reputation: 7979

with iteration you could use an array-formula like: (for row 3)

=INDIRECT("R3C"&MAX(COLUMN(3:3)*(LEN(3:3)>0))-1, 0)

without you need to exclude the cell itself... having it at column C it would be something like: (for row 3)

=INDIRECT("R3C"&MAX(COLUMN($D3:$ZZ3)*(LEN($D3:$ZZ3)>0))-1, 0)

if you want to simply auto fill the formula down then replace the "R3C" with "R"&ROW()&"C"

The formulas shown here are array formulas and must be confirmed with Ctrl+Shift+Enter.

Upvotes: 0

Related Questions