Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-17-2021, 08:51 AM
beginner beginner is offline List of N last cells Windows 7 32bit List of N last cells Office 2013
Advanced Beginner
List of N last cells
 
Join Date: Sep 2011
Location: Europe
Posts: 45
beginner will become famous soon enough
Default List of N last cells

How to return a list of the last three cells of nuninterrupted sequence consecutive cells in the column. I also need a variant, how to return a value from the last 6 cells of an uninterrupted sequence consecutive cells.
So I want to have a choice of returned cells (i.e. condition). The condition is located in the D2 cell. Please formula for Excel 2013.

Example in the A column



Data
10
25
Total 15
30
38
Total 8
29
47
Total 18
16
32
Total 16
19
75
Total 56
48
99
Total 51
Empty/Blank cell
Empty/Blank cell
Total 0
Empty/Blank cell
Empty/Blank cell
Total 0
etc...





Expected results


Three of the last filled cells in continuous sequence
48
99
Total 51

Six of the last filled cells in continuous sequence
19
75
Total 56
48
99
Total 51
Reply With Quote
  #2  
Old 06-17-2021, 09:23 AM
Purfleet Purfleet is offline List of N last cells Windows 10 List of N last cells Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 335
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Please add a workbook, it is so much easier than recreating data!!!!!!!
Reply With Quote
  #3  
Old 06-17-2021, 09:37 AM
Purfleet Purfleet is offline List of N last cells Windows 10 List of N last cells Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 335
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Not the best, but a start

for 3 - =INDEX(A:A,MATCH(TRUE,A:A="",0)-ROWS(C1:$C$3))
for 6 - =INDEX(A:A,MATCH(TRUE,A:A="",0)-ROWS($C1:C$6))
Attached Files
File Type: xlsx List of N last cells_ar12.xlsx (9.9 KB, 4 views)
Reply With Quote
  #4  
Old 06-17-2021, 12:01 PM
beginner beginner is offline List of N last cells Windows 7 32bit List of N last cells Office 2013
Advanced Beginner
List of N last cells
 
Join Date: Sep 2011
Location: Europe
Posts: 45
beginner will become famous soon enough
Default

Quote:
Originally Posted by Purfleet View Post
Not the best, but a start
Purfleet, thank you
These array formulas work
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
First of all check to cells and after drop down list DIMI Excel Programming 2 11-27-2019 12:15 AM
How to match one list with another and return certain values from corresponding cells in excel Oluagbe1 Excel 10 02-11-2019 07:00 PM
Formating Partial cells in a large list Zajac Excel 3 08-16-2016 09:13 AM
List of N last cells convert list in one cell into multiple cells RayK Excel 2 02-09-2016 04:53 AM
Drop down list to overlay column of cells with different formulae TomJ Excel 0 12-19-2015 08:55 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:48 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft