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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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

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
331
Moley84
M
M
  • Question Question
I actually need to show duplicates in pivot tables
Replies
3
Views
352
matts12
M
R
  • Question Question
Move row to bottom of range is cell value is zero
Replies
6
Views
270
Peter_SSs
Peter_SSs
L
  • Solved
Insert formulas to new lines in table
Replies
3
Views
140
Ltuk85
L
A
  • Solved
setting a range from Active cell to last cell when Active cell change columns
2
Replies
12
Views
464
jolivanes
jolivanes

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

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