+ Reply to Thread
Results 1 to 6 of 6

Combine Ranges into an Array

  1. #1
    Registered User
    Join Date
    07-31-2009
    Location
    San Jose, CA
    MS-Off Ver
    Excel 2003
    Posts
    14

    Combine Ranges into an Array

    I could not find an answer to my question by search.

    Example:

    I have four unsorted data ranges in Column B in the attached file, "Project1", "Project2", "Project3", "Project4"

    I want to create an unsorted Array in Column D, ignoring spaces and #N/A in Column B
    Attached Files Attached Files

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Combine Ranges into an Array

    mperata,

    Attached is a modified version of your example workbook.

    Column C is being used as a helper column to eliminate the error cells. Then column E creates the desired array.

    In cells C5 and copied down is this formula:
    Please Login or Register  to view this content.

    In cell E5 and copied down is this array formula (Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter):
    Please Login or Register  to view this content.


    You can hide column C if you want. Does that work for you?
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Combine Ranges into an Array

    Here's the formula without a helper column (still an array formula):
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    07-31-2009
    Location
    San Jose, CA
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Combine Ranges into an Array

    Thanks tigeravatar

    I do not understand this part of your formula "ROW()-4)"

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Combine Ranges into an Array

    Row() gets the current row. The formula starts in row 5, so that returns 5. Then it subtracts 4 so that the row the formula starts in will return a 1:

    row 5 -> Row()-4 = 5-4 = 1
    row 6 -> Row()-4 = 6-4 = 2
    row 7 -> Row()-4 = 7-4 = 3
    etc

    The reason we want that is so the Small(array,#) will return the correct #. It needs to be:
    Small(array,1)
    Small(array,2)
    Small(array,3)
    etc

    So basically, because the formula starts in row 5, it is Row()-4
    If the formula started in row 2, it would be Row()-1
    If the formula started in row 3, it would be Row()-2

  6. #6
    Registered User
    Join Date
    07-31-2009
    Location
    San Jose, CA
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Combine Ranges into an Array

    Got it, thanks!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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