Results 1 to 7 of 7

Thread: Split Data

  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Excel Experts,

    I have a database of 1500 employee with their Managers name in the subsequent column. There are close to 300 Managers. I need to split the data into 300 different sheets based on the manager name. One of the process is to create a Pivot Table and double click for each manager. This is very time consuming. Is there a simpler way of doing the same.

    A simpler solution will save a lot of time for me, since i also need to send this sheets to the respective mails by email.

    Thanks
    Baiju

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Make Manager the page field in the pivot table (i.e. move it to the Page area in the Pivot Table wizard).

    [attachment=85913:x.png]

    Then select Pivot Table | Show Pages... from the Pivot Table toolbar
    Attached Images Attached Images
    • File Type: png x.png (4.2 KB, 1 views)

  3. #3
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Hans,

    I have done this, this particular functionality only creates seperate pivots for each manager. I need the data for each manager on different sheets. Is that possible.

    Thanks
    Baiju

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Drag all remaining fields to the row area before clicking the show pages option.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use VBA code to split data into worksheets. See Post 471071 for an example; you would have to modify it to suit your needs.

  6. #6
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Hans,

    Just was about to post a query asking if there was any other way of spliting the data based on the Manager, since the pivot table will not have the formatting.

    I am not good at macros, infact i do not know how to write or edit a macro. Will it be possible for you to help me on the same if i provide you with a sample data, which i could further apply to my original data.

    Thanks
    Baiju

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you post a small sample workbook, we can see what needs to be done.

Posting Permissions

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