+ Reply to Thread
Results 1 to 5 of 5

Find the third and fourth non-blank cells in a column and then enter each result in a cell

  1. #1
    Registered User
    Join Date
    01-04-2017
    Location
    Socal
    MS-Off Ver
    2013 excel
    Posts
    4

    Question Find the third and fourth non-blank cells in a column and then enter each result in a cell

    Hello All,
    I have been on numerous websites all day but have not been successful in finding the answer to my problem.

    Problem:
    I need help to find the third and fourth non-blank cell in a range in, see attachment.
    In range E12:E61, there are a series of blank and non-blank cells.
    I need a formula to find the third non-blank cell, which in this example is M5-M5 and then enter the result, M5-M5, in cell E2
    Similarly, I need a formula to find the fourth non-blank cell, which in this example is M7-M7 and then enter the result, M7-M7 in cell E4.
    Now all the cells in range E12:E61 have formulas in them so the third and fourth non-blank cell could change depending on other conditions.

    Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find the third and fourth non-blank cells in a column and then enter each result in a

    Try these array formulas**:

    For the 3rd non-empty cell:

    =IFERROR(INDEX(E:E,SMALL(IF(E12:E61<>"",ROW(E12:E61)),3)),"")

    For the 4th non-empty cell:

    =IFERROR(INDEX(E:E,SMALL(IF(E12:E61<>"",ROW(E12:E61)),4)),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Find the third and fourth non-blank cells in a column and then enter each result in a

    Hi,

    One way as an array formula - entered with Ctrl-Shift-Enter in E2

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    for E4 change the 3 to a 4
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    01-04-2017
    Location
    Socal
    MS-Off Ver
    2013 excel
    Posts
    4

    Thumbs up Re: Find the third and fourth non-blank cells in a column and then enter each result in a

    Hello,
    Fist thank you both for your response.

    Tony, your formulas worked for both answer I was looking for so I went with it. Can you give me a brief explanation of your formula?

    Richard, your formula was the most common format style that I found when I was searching the web for answers but none of them quite worked the way I needed them to. But yours did work for finding the
    third non-blank cell. But when I changed the 3 to 4 for E4, the answer is blank, so it did not work for the fourth.
    Do you have a fix for it?

    Thanks,
    Noel

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find the third and fourth non-blank cells in a column and then enter each result in a

    Quote Originally Posted by wpssnotoq View Post
    Tony, your formulas worked for both answer I was looking for so I went with it. Can you give me a brief explanation of your formula?
    =IFERROR(INDEX(E:E,SMALL(IF(E12:E61<>"",ROW(E12:E61)),3)),"")

    The IF function tests the cells in the range to see if they are not blank/empty.

    Where this condition is TRUE it returns that row number.

    The array of row numbers is then passed to the SMALL function where it extracts the 3rd and 4th smallest row numbers.

    This number is then passed to the INDEX function will tells it to return the value in column E from row number N.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to find the last cell value in a column range that contains blank cells
    By Bonzopookie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-29-2014, 07:50 PM
  2. for the result i have enter 10 cell, plz find attachment
    By gsrikanth in forum Excel General
    Replies: 2
    Last Post: 05-09-2014, 07:04 AM
  3. Find & select 1st blank cell in a range of formulated column cells
    By jnbroek in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-19-2013, 09:26 AM
  4. Replies: 7
    Last Post: 08-14-2013, 05:41 AM
  5. Find the value of the 2nd to last cell in a column that includes blank cells
    By LookIWashed4Supper in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-23-2013, 09:35 PM
  6. [SOLVED] Find first non-blank cell after a group of blanks copy offset cells, find next blank, loop
    By gwsampso in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-21-2012, 06:11 AM
  7. Macro formula to match two columns and Produce result in fourth column.
    By rishi1983 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-03-2012, 08:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1