Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 18th 11, 04:38 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2011
Posts: 1
Default Return Each Last Non-blank Cell, Value 2 Column Offset, Many GAPS

Looking for a formula that will look down a column of cells that has
many blanks gapped in-between other cells containing text, numbers,
etc and for each block of cells with continuous data, find the last
non-blank cell and return a value that is 2 columns away in the same
row as the last non-blank cell. To clarify, there may be data that
extends down thousands of cells and the blocks of non-blank data in
the applicable column may be as few as 1 continuous or as many as 100
continuous pieces of data and I am only wanting to pull the source
cell that is adjacent to the last data populated cell in each block,
but for all blocks down the spreadsheet.

  #2   Report Post  
Old August 18th 11, 06:37 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 587
Default Return Each Last Non-blank Cell, Value 2 Column Offset, ManyGAPS

hi,

array formula to validate with "ctrl + shift + enter"

=INDEX(C:C,MAX(IF(A3:C100<"",ROW(A3:C100))),1)

--
isabelle



Le 2011-08-18 11:38, Jim a écrit :
Looking for a formula that will look down a column of cells that has
many blanks gapped in-between other cells containing text, numbers,
etc and for each block of cells with continuous data, find the last
non-blank cell and return a value that is 2 columns away in the same
row as the last non-blank cell. To clarify, there may be data that
extends down thousands of cells and the blocks of non-blank data in
the applicable column may be as few as 1 continuous or as many as 100
continuous pieces of data and I am only wanting to pull the source
cell that is adjacent to the last data populated cell in each block,
but for all blocks down the spreadsheet.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to return the last non-blank cell in a column that is TEXT VSlaybaugh Excel Worksheet Functions 6 October 8th 08 03:34 PM
Formula to return position of the next Non-blank cell in a column PCLIVE Excel Worksheet Functions 14 July 17th 07 01:11 PM
Return next non-Blank Cell in a Column ? Jakobshavn Isbrae Excel Worksheet Functions 3 May 23rd 07 10:28 PM
offset 1 row and return to first column GregM Excel Discussion (Misc queries) 1 March 31st 06 01:37 AM
Transpose Column With Gaps to Row With No Gaps? SteveC Excel Discussion (Misc queries) 5 June 18th 05 01:01 AM


All times are GMT +1. The time now is 12:54 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017