Find second last value in the column

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDE
1X
2P1
3P1
4P1
51
6X
7X
8X
92
101
11X
12X
131
14
15
16
Sheet11


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

Regards,</SPAN>
Kishan</SPAN>
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
This is an array formula that must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
ABCDE
1X
21X
3x
4
51
6X
7
8X
92
101
11X
12X
131
Sheet
 
Upvote 0
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
ABCDE
1X
2P1#NUM!
3P1
4P1
51
6X
7X
8X
92
101
11X
12X
131
14
15
16
Sheet11
Cell Formulas
RangeFormula
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>
 
Upvote 0
Here is a VBA solution for you

Code:
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
 
Upvote 0
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
ABCDE
1X
2P1X
3P1
4P1
51
6X
7X
8X
92
101
11X
12X
131
14
15
16
Sheet11
Cell Formulas
RangeFormula
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>
 
Upvote 0
Here is a VBA solution for you

Code:
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 :grin:
</SPAN></SPAN>
 
Upvote 0
Hi alansidman, I like the VBA it is nice solution!
In that case, here is another VBA solution for you to consider...
Code:
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...
Code:
Sub FindX()
  Range("C1") = Cells(Range("C" & Rows.Count).End(xlUp).Row - 1, "C")
End Sub
 
Last edited:
Upvote 0
How about a "regular" formula


=LOOKUP(1,1/(C5:C1000<>(LOOKUP(1,1/(C5:C1000<>""),C5:C1000))),C5:C1000)
 
Upvote 0
Another VBA solution:

Code:
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))
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top