+ Reply to Thread
Results 1 to 8 of 8

Automatically fill non empty cells

  1. #1
    Registered User
    Join Date
    09-30-2018
    Location
    Veliko Tarnovo
    MS-Off Ver
    Office 2010
    Posts
    4

    Automatically fill non empty cells

    Hi i have to following table :
    empty 4 4 empty 4 empty
    empty 4 empty 4 empty 5
    empty 2 empty 3 empty 3
    empty empty 6 3 empty 23




    I need in the next columns to be filled only data where its not empty like:

    empty 4 4 empty 4 empty 4 4 4
    empty 4 empty 4 empty 5 4 4 5
    empty 2 empty 3 empty 3 2 3 3
    empty empty 6 3 empty 23 6 3 23



    Thanks
    Last edited by digit99; 09-30-2018 at 03:21 PM.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Automatically fill non empty cells

    So you want to duplicate the table with the empty cells omitted?

    Are those empty cells really empty, or do they contain formulas that appear as empty?

  3. #3
    Registered User
    Join Date
    09-30-2018
    Location
    Veliko Tarnovo
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: Automatically fill non empty cells

    Yes, i want table only with numbers. Cells are really empty! Not with a value "Empty"

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Automatically fill non empty cells

    Sorry, I did't mean the word 'Empty', but that it looks empty, for example a formula that shows "" as the result when the criteria of the formula is not met.

    Assuming that your sample data is in the range A1:F4, enter this array formula into G1. Once the array is confirmed, drag right and down as needed.

    =IFERROR(INDEX($A1:$F1,SMALL(IF($A1:$F1<>"",COLUMN($A1:$F1)),COLUMNS($G1:G1))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Last edited by jason.b75; 09-30-2018 at 03:55 PM.

  5. #5
    Registered User
    Join Date
    09-30-2018
    Location
    Veliko Tarnovo
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: Automatically fill non empty cells

    OK, i have a table with many columns - from A1 to AX1 , but only in G1 i have a value "2", in N1 i have a value "23" and in AP1 i have a value "4" all other cells are empty. I need next columns - AY1 to be filled with value "2", AZ1 to be filled with value "23" and in BA1 to be filled with value "4".

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Automatically fill non empty cells

    Formula from my previous post, edited to match the ranges you have provided

    =IFERROR(INDEX($A1:$AX1,SMALL(IF($A1:$AX1<>"",COLUMN($A1:$AX1)),COLUMNS($AY1:AY1))),"")

    Array confirm this in AY1 following the instructions in my previous post, then use the fill handle to copy it across to AZ1 and BA1, then fill down as needed for subsequent rows.

  7. #7
    Registered User
    Join Date
    09-30-2018
    Location
    Veliko Tarnovo
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: Automatically fill non empty cells

    Many thanks But my wrong... Other values is not empty ... its 0 ! I was not see .. terribly sorry !

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Automatically fill non empty cells

    In that case try changing <>"" to <>0

    =IFERROR(INDEX($A1:$AX1,SMALL(IF($A1:$AX1<>0,COLUMN($A1:$AX1)),COLUMNS($AY1:AY1))),"")

    Whatever is immediately after <> will be ignored.

    Array confirmed as before.

+ 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. copy current cells and fill the empty cells below it consecutively
    By jitterbug888 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-09-2016, 10:34 PM
  2. Fill empty cells..
    By Ootso in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-14-2015, 04:30 AM
  3. [SOLVED] Fill Empty Cells
    By srikanthk in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-30-2012, 08:13 AM
  4. [SOLVED] Fill empty cells with content of full cells above it
    By julia81 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-17-2011, 03:57 PM
  5. Fill empty cells with a 0
    By vbjon in forum Excel General
    Replies: 3
    Last Post: 08-05-2008, 10:44 PM
  6. [SOLVED] Fill empty cells
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-23-2006, 11:50 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