Microsoft Office Forums List of N last cells
 Register FAQ Search Today's Posts Mark Forums Read

#1
06-17-2021, 08:51 AM
 beginner Windows 7 32bit Office 2013 Advanced Beginner Join Date: Sep 2011 Location: Europe Posts: 45
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
#2
06-17-2021, 09:23 AM
 Purfleet Windows 10 Office 2019 Expert Join Date: Jun 2020 Location: Essex Posts: 339

Please add a workbook, it is so much easier than recreating data!!!!!!!
#3
06-17-2021, 09:37 AM
 Purfleet Windows 10 Office 2019 Expert Join Date: Jun 2020 Location: Essex Posts: 339

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
 List of N last cells_ar12.xlsx (9.9 KB, 4 views)
#4
06-17-2021, 12:01 PM
 beginner Windows 7 32bit Office 2013 Advanced Beginner Join Date: Sep 2011 Location: Europe Posts: 45

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

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post DIMI Excel Programming 2 11-27-2019 12:15 AM Oluagbe1 Excel 10 02-11-2019 07:00 PM Zajac Excel 3 08-16-2016 09:13 AM RayK Excel 2 02-09-2016 04:53 AM TomJ Excel 0 12-19-2015 08:55 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:13 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top