+ Reply to Thread
Results 1 to 6 of 6

Extract data from a horizontal range with blanks and create a vertical list

  1. #1
    Registered User
    Join Date
    03-07-2013
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Extract data from a horizontal range with blanks and create a vertical list

    Hi all

    I have built a milestone chart in excel (see attached) which consists of projects down the left, months across the top and text in cells under some months, but most cells are blank.

    I would like to extract all the text from the cells and put it into a vertical list next to the appropraite projects.

    Please refer to the attached sheet for details.

    Any ideas how to do this?

    Many thanks








    Milestone chart example.xlsx

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Extract data from a horizontal range with blanks and create a vertical list

    Try this one. Please note that as you have merged cells, the project names in column A should remain on the same position

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    03-07-2013
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Extract data from a horizontal range with blanks and create a vertical list

    Fantastic, it works a treat! many thanks.

    Could you please explain what it's actually doing? There's a lot in there i don't recognise.

    Also is it possible to have the relevant project name next to each milestone?

    many thanks

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Extract data from a horizontal range with blanks and create a vertical list

    "Could you please explain what it's actually doing? There's a lot in there i don't recognise".
    It is an array code. You assign the selected range in to an array and you then loop through columns for each row. If a cell is empty, the code ignores it and jumps in to the next cell. Once the code finishes the loop, it writes back the array in to cells.

    "Also is it possible to have the relevant project name next to each milestone?!.
    Sorry! I could not align them as you have merged cells. One solution would be to unmerge the project name and then you merged them and then aligning them with each milestone.

  5. #5
    Registered User
    Join Date
    03-07-2013
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Extract data from a horizontal range with blanks and create a vertical list

    Thanks AB33, you've been a real help here. I'm going through line by line trying to understand what is going on as my actual milestone chart is much bigger than the example i put online so i need to adapt the code to make it work.

    The fact that you have done it with such a few lines of code is amazing and i'd like to be able to replicate it. Would you be able to explain line by line what the code is actually doing? I've never used arrays in vba before.

    Many thanks for your time in this.

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Extract data from a horizontal range with blanks and create a vertical list

    Please Login or Register  to view this content.

+ 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