Find second last value in the column

[Kishan]

Hi,</SPAN></SPAN>

I need a formula that can find second last value in the column either it is number or alphabets.</SPAN></SPAN>

Resulting data...cell </SPAN></SPAN>C1

Book1
	A	B	C	D	E
1			X
2			P1
3			P1
4			P1
5			1
6			X
7			X
8			X
9			2
10			1
11			X
12			X
13			1
14
15
16
Sheet11

Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN>

 

[AhoyNC]

This is an array formula that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E
1			X
2			1		X
3			x
4
5			1
6			X
7
8			X
9			2
10			1
11			X
12			X
13			1
Sheet

 

[Kishan]

This is an array formula that must be entered with CTRL-SHIFT-ENTER.

Hi AhoyNC, I entered formula with CTRL-SHIFT-ENTER. But it return me #¡NUM!</SPAN></SPAN>

Please can you check it?</SPAN></SPAN>

Book1
	A	B	C	D	E
1			X
2			P1		#NUM!
3			P1
4			P1
5			1
6			X
7			X
8			X
9			2
10			1
11			X
12			X
13			1
14
15
16
Sheet11

Cell Formulas
Range	Formula
E2	{=INDEX(C:C,LARGE(IF(C:C<>"",ROW(C:C)),2))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[alansidman]

Here is a VBA solution for you

Option Explicit


Sub FindX()
    Dim lr As Long, x As Long, y As Variant
    lr = Range("C" & Rows.Count).End(xlUp).Row
    x = lr - 1
    y = Range("C" & x)
    Range("C1") = y
End Sub

 

[Kishan]

Hi AhoyNC, I entered formula with CTRL-SHIFT-ENTER. But it return me #¡NUM!</SPAN></SPAN>
Please can you check it?</SPAN></SPAN>Kind Regards,</SPAN></SPAN>Kishan</SPAN></SPAN>

Hi AhoyNC, altering the formula as below it worked fine</SPAN></SPAN>

Book1
	A	B	C	D	E
1			X
2			P1		X
3			P1
4			P1
5			1
6			X
7			X
8			X
9			2
10			1
11			X
12			X
13			1
14
15
16
Sheet11

Cell Formulas
Range	Formula
E2	{=INDEX(C5:C65536,LARGE(IF(C5:C65536<>"",ROW(C5:C65536)),6))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you for your help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[Kishan]

Here is a VBA solution for you

Option Explicit


Sub FindX()
    Dim lr As Long, x As Long, y As Variant
    lr = Range("C" & Rows.Count).End(xlUp).Row
    x = lr - 1
    y = Range("C" & x)
    Range("C1") = y
End Sub

Hi alansidman, I like the VBA it is nice solution! </SPAN></SPAN>

Thank you for your help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[Rick Rothstein]

Hi alansidman, I like the VBA it is nice solution!

In that case, here is another VBA solution for you to consider...

Sub FindNextToLastInColumn()
  Dim Arr As Variant
  Arr = Range("C1", Cells(Rows.Count, "C").End(xlUp))
  Range("C1") = Arr(UBound(Arr) - 1, 1)
End Sub

By the way, alansidman's approach can be reduced to a one-liner program...

Sub FindX()
  Range("C1") = Cells(Range("C" & Rows.Count).End(xlUp).Row - 1, "C")
End Sub

 

[DanteAmor]

Another VBA solution:

Sub penultimate()
    [C1] = Range("C" & Rows.Count).End(xlUp)(0)
End Sub

 

[DanteAmor]

How about a "regular" formula


=LOOKUP(1,1/(C5:C1000<>(LOOKUP(1,1/(C5:C1000<>""),C5:C1000))),C5:C1000)

 

[Rick Rothstein]

Another VBA solution:

Sub penultimate()
    [C1] = Range("C" & Rows.Count).End(xlUp)(0)
End Sub

Hmm, index 0, eh? I am almost embarrassed to say that in all my time using that shortcut notation, it never occurred to me to try index 0 for the cell above the referenced cell (and I am not usually that rigid in my thinking).

How about a "regular" formula

=LOOKUP(1,1/(C5:C1000<>(LOOKUP(1,1/(C5:C1000<>""),C5:C1000))),C5:C1000)

Since there are no blanks in the OP's data, this regular formula should also work...

=INDEX(C:C,COUNTA(C2:C1000))