How do i get the last 5 cells out of a column

D

dannyi21i

New Member
Joined
Feb 25, 2018
Messages
17
in my work sheet i want to get the last 5 cells of a column and display them in 5 other cells.

i used the formula below and dragged it down 5 to show the last 5 cells and it worked to an extent but if the last 5 cells are all the same it messes up and doesnt show the last 5 cells;

=INDEX(AC51:AC76,MATCH(99^99,AC51:AC76,1)-1)

please can anyone help me?
 
Sort by date Sort by votes
still coming up with '0'

i thought it woud have the code below but it comes up with '0' on that aswell.

=INDIRECT("AC" & COUNTA(AC:AC)+29-(ROW()-52))
 
Last edited:
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
dannyi21i said:
9
9
http://tinypic.com/r/rlkmkh/9

g4 to g8 is were it should show the last 5

Click to expand...



G4: =INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G4)+1)


Excel 2013/2016
ABCDEFGH
1
2
3letter
4te
5br
6at
7nn
8nn
9t
10r
11e
Sheet1
 
Upvote 0
VBA Geek said:
G4: =INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G4)+1)

Excel 2013/2016
ABCDEFGH
te
br
at
nn
nn
t
r
e

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]letter[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​
Click to expand...

that just comes back with true and false
 
Upvote 0
dannyi21i said:
that just comes back with true and false
Click to expand...

you dont have to input the formula G4=INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G4)+1)

I meant that in the cell G4, you input the formula=INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G4)+1)
 
Last edited:
Upvote 0
MARK858 said:
It doesn't for me, it returns...

Sheet1

ABCDEFG
letter
t e
b r
a t
n n
n n
t a
r b
e t

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
G4=INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G4)+1)
G5=INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G5)+1)
G6=INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G6)+1)
G7=INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G7)+1)
G8=INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G8)+1)
G9=INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G9)+1)
G10=INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G10)+1)
G11=INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G11)+1)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...


i have adapted this to the way i want it and it works perfectly so far, thankyou so much!
 
Upvote 0
Hi dannyi21i,
give this formula a try
=INDEX($C$4:$C$11,LARGE(ROW($C$4:$C$11)-ROW($C$3),ROW(A1)))
It must be entered with CTRL SHIFT ENTER and then drag it down.

hth
 
Last edited:
Upvote 0
VBA Geek said:
G4: =INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G4)+1)

Excel 2013/2016
ABCDEFGH
te
br
at
nn
nn
t
r
e

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]letter[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​
Click to expand...


thanks mate this code helped me out so much been trying to figure the code i need for weeks.
 
Upvote 0
B___P said:
Hi dannyi21i,
give this formula a try
=INDEX($C$4:$C$11,LARGE(ROW($C$4:$C$11)-ROW($C$3),ROW(A1)))
It must be entered with CTRL SHIFT ENTER and then drag it down.

hth
Click to expand...


this code just came back with '0's mate but i found the code i need from someone else who commented above, thankyou for your efforts though.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Conditional formatting using icon set to show trend AND colour coding of cell based on a %
Replies
1
Views
205
etaf
etaf
R
  • Solved
Cells in the same column showing different date format
Replies
5
Views
81
Peter_SSs
Peter_SSs
M
  • Solved
VBA Use formula within all cells within a range (across numerous columns and rows), referencing row 5 in given column
Replies
4
Views
372
Moley84
M
SanjayGMusafir
  • Solved
Go to last Cell of Current Table
2
Replies
17
Views
202
SanjayGMusafir
SanjayGMusafir
M
  • Question Question
I actually need to show duplicates in pivot tables
Replies
3
Views
379
matts12
M

Forum statistics

Threads
1,224,828
Messages
6,181,201
Members
453,022
Latest member
RobertV1609

Share this page

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