+ Reply to Thread
Results 1 to 8 of 8

Returning Non-blank Cells from a Row Section to another Row Section on the Same Row

  1. #1
    Registered User
    Join Date
    05-12-2017
    Location
    Hong Kong
    MS-Off Ver
    2016
    Posts
    12

    Returning Non-blank Cells from a Row Section to another Row Section on the Same Row

    Hello everyone,

    I am using Microsoft Excel 2007.
    How can I return non-blank cells from a row section to another row section on the same row with formulas?
    From Column B to Column H I would like to have the non-blank cells all listed one after one.
    From Column I to Column O contains the data to be listed.
    And each row has its own set of data to be listed.
    The attached excel file simulates the the expected output.
    I have been looking for the relevant information online but after a lot of testings all the existing similar solutions do not seem to work for me.
    I am not sure about the reason but I guess maybe it is because the existing formulas available online return non-blank cells from a column section to a column section rather than from a row section to a row section.

    Thanks a lot,
    Lawrence
    Last edited by LawCarrot; 05-14-2017 at 10:26 PM.

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,265

    Re: Returning Non-blank Cells from a Row Section to another Row Section on the Same Row

    Hope this works

  3. #3
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,732

    Re: Returning Non-blank Cells from a Row Section to another Row Section on the Same Row

    .
    .
    You indicated a Formula solution; however, here is a macro solution just in case :

    Paste into a routine module.

    Please Login or Register  to view this content.

  4. #4
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Returning Non-blank Cells from a Row Section to another Row Section on the Same Row

    Assuming you have headers, select B2 and paste this ARRAY FORMULA in the formula bar:
    =IFERROR(INDEX($A2:$O2,,SMALL(IF($I2:$O2<>"",COLUMN($I2:$O2)),COLUMNS($A2:A2))),"")

    Now press CTRL+SHIFT+ENTER to confirm (as always required with array formulae), then drag across to H2.

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 05-14-2017 at 10:26 PM.

  5. #5
    Registered User
    Join Date
    05-12-2017
    Location
    Hong Kong
    MS-Off Ver
    2016
    Posts
    12

    Re: Returning Non-blank Cells from a Row Section to another Row Section on the Same Row

    Thank you azumi, you help me a lot!

  6. #6
    Registered User
    Join Date
    05-12-2017
    Location
    Hong Kong
    MS-Off Ver
    2016
    Posts
    12

    Re: Returning Non-blank Cells from a Row Section to another Row Section on the Same Row

    And thank you Logit and leelnich for offering help too!

  7. #7
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,732

    Re: Returning Non-blank Cells from a Row Section to another Row Section on the Same Row

    You are welcome

  8. #8
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Returning Non-blank Cells from a Row Section to another Row Section on the Same Row

    No problemo.

+ 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] sort a section if cells in section contains certain values
    By vitaudi in forum Excel General
    Replies: 9
    Last Post: 10-08-2014, 03:15 AM
  2. Hide a section based on the click of a box in the beginning of the section
    By schmidt1962 in forum Word Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2014, 04:51 PM
  3. [SOLVED] Replace blank cells with a value in different section
    By thelegazy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-30-2013, 10:46 AM
  4. Replies: 1
    Last Post: 06-16-2013, 07:36 AM
  5. Deleting Row Section without Disrupting Formula Range Outside of Deleted Section
    By JeffNYG23 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-14-2013, 09:10 AM
  6. Moving section headings from below section to above section..
    By Fayebaline in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-24-2011, 06:58 AM
  7. move that Row From the Waiting section to the completed section automaticly
    By jjsaw5 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-21-2007, 06: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