+ Reply to Thread
Results 1 to 4 of 4

Need to capture all non-empty cells in a row

  1. #1
    Registered User
    Join Date
    02-01-2021
    Location
    New York, USA
    MS-Off Ver
    MS Office 2013
    Posts
    2

    Need to capture all non-empty cells in a row

    I have a row of data with a lot of blanks in it, similar to this: 10 20 25 "" "" 24 "" 99 "" "" "" 44.

    A few rows above it, I have a list of dates, so: 1/1/2020 1/2/2020 (and so on).

    What I want to do is grab all the numbers and their accompanying dates, without capturing the blanks at all.

    So my Excel sheet would look like:

    1/1/2020 1/2/2020 1/3/2020 1/6/2020 1/8/2020 1/12/2020
    10 20 25 24 99 44


    Instead of:

    1/1/2020 1/2/2020 1/3/2020 1/4/2020 1/5/2020 1/6/2020 1/7/2020 1/8/2020
    10 20 25 0 0 24 0 99


    This formula grabs the first non-blank cell, so it gives me 10:
    =INDEX(G2:FM2,MATCH(FALSE,ISBLANK(G2:FM2),0))

    And this formula grabs me the accompanying date:
    =INDEX(G1:FM1,MATCH(FALSE,ISBLANK(G2:FM2),0))

    But I'm struggling with how to pull the formulas forward so it keeps going until it runs out of non-blank cells to pull from.

    I tried this instead, but it repeats non-blank cells I've already used instead of giving me the next non-blank. For instance, 24 repeated a few times before going onto 99, instead of just skipping the blank days altogether.

    =IF(G2<>"",G2,INDEX(G2:$Q$2,MATCH(FALSE,ISBLANK(G2:$Q$2),0)))

    Please advise.

    Thank you!

  2. #2
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Need to capture all non-empty cells in a row

    To list values in row 2 under non-empty cells in row 1, use this array formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Paste it in any blank cell, then confirm with Ctrl+Shift+Enter instead of just Enter.
    Then copy across.

    Good luck!

  3. #3
    Registered User
    Join Date
    02-01-2021
    Location
    New York, USA
    MS-Off Ver
    MS Office 2013
    Posts
    2

    Re: Need to capture all non-empty cells in a row

    Thank you! This is amazing! Is there a way I can do the same thing but reference a row name instead of using cell references? Like instead of G1: FM1, can I reference a row called "Department 1"?

  4. #4
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Need to capture all non-empty cells in a row

    You're welcome.
    If Department 1 is a named row range, I can't see why not.
    If you have problems with that, we're here to help.

+ 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. To Skip Empty Cells & blank Spaces and Capture Values
    By fareedexcel in forum Excel General
    Replies: 5
    Last Post: 12-26-2020, 09:56 AM
  2. Formula to Capture Only Dates With Corresponding Numbers (no empty cells)
    By LEORITY in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 06-15-2017, 01:18 AM
  3. [SOLVED] 8 Columns with empty cells -> 1 Column without empty cells -> Problem: 1st value ignored
    By chrisignm in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-23-2015, 04:50 AM
  4. Replies: 1
    Last Post: 05-26-2014, 01:31 PM
  5. Need to find empty filled cells in a column and copy to the empty cell below
    By Grahamfeeley in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-30-2014, 10:09 AM
  6. capture data from userform in next empty column
    By ciapul12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-05-2013, 07:42 AM
  7. macro to colour empty cells (cells not recognized as empty)
    By Gerben in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-30-2005, 11:05 AM

Tags for this Thread

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