Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Extract data from multiple sheets

    I have a workbook with 30 sheets. 26 of them are labeled AA, BB, CC, etc.

    Row 1 in each of the sheets is a column heading.
    In column B of each of those 26 sheets I have a "specialty" that is associated with the information in the rows. There are 132 specialties. Each alpha-tab doesn't have all of the specialties, necessarily...sometimes only 1, sometimes 3, sometimes 50.

    I want a new workbook with each tab being a specialty with columns A-G from each of the alphabetical tabs in those respectively.

    Guessing it's a macro? Otherwise, it's a bit of labor as I haven't this early morning come up with a slick formula process to do it.

    TIA
    Last edited by kweaver; 2015-01-28 at 10:39.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi kweaver

    I can't see how this could be done without vba.
    My understanding is:
    You want a new workbook, with 132 sheets, each sheet being a 'speciality' (i.e. sheet tab name=speciality).
    then for each sheet in the new workbook, you want to 'filter-each-sheet-in-original-workbook-and-filter-extract-and-append-below' for the corresponding 'speciality' (extracting columns A to G only from source workbook)????
    If so, do you have a list of all the 'speciality' values, or, do you want the sheets to be created 'as required' on-the-fly as each sheet in the original source workbook is processed??

    zeddy
    Last edited by zeddy; 2015-01-28 at 11:43.

  3. #3
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    I strongly suggest ONE worksheet with all and an extra column to identify the "sheet" Post file.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Zeddy: I have a list of them all, and could put that in the new or old workbook in a separate sheet so that the new workbook could have the tabs named that way (with, of course, the size restriction on the tab name).

  5. #5
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Post a file
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    The data is confidential so I can't post a real file.

    I did what I needed to do by grabbing ALL of the data from the 26 sheets into one matrix.
    Moved the array to APL (programming language for array processing) and broke out all 132 specialties with a simple program.

    Then, linked to Excel from APL and moved each of them to separate sheets.

    Have to love array processing language when you don't know VBA! Always comes to my rescue except I would like to use Excel only out of curiosity and education.
    Last edited by kweaver; 2015-01-28 at 19:08.

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    KW,

    Here is a sample worksheet that has 3 sheets with specialties in columns B and associated data on the rows. The code will create a list of unique sorted specialties and open a new workbook with a worksheet for each specialty. Using the names of each new worksheet (Specialty), the source workbook will be searched for all the matching specialties and when found, the associated row will be appended to that sheet in the new workbook.

    You can have any number of worksheets in the source workbook, any number of rows, any number of unique Specialties (=number of new worksheets)(up to the memory limitations of your system).

    HTH,
    Maud
    Attached Files Attached Files

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    kweaver (2015-01-28)

  9. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Maud: slick. Thanks.

    P.S. How's the snow there?

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    The 12"-20" that was forecasted turned out to be a bust. We ended up with about an inch and a little bit more on the way. Must be nice on the southern west coast.

    Let me know if I can be of any help.

    Maud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •