Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts

    pivot table (xp professional)

    Hi, Is it possible to create pivot table with multiple sheets? i have a huge data with as much as 2000000 rows and want to summarize it with help of pivt table. pls help me out.
    Regards
    Prasad

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: pivot table (xp professional)

    It can be done (selct "Multiple consolidation ranges") but it is more limited. The help has additional details

    Steve

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: pivot table (xp professional)

    If you can move the data to a database then you can build a pivot table off an external data source. Alternatively you can use ADO with a union query to create a recordset and assign that to your pivotcache.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pivot table (xp professional)

    I am interested with this, I hope someone could elaborate with example, please..

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: pivot table (xp professional)

    Try here and here for example.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pivot table (xp professional)

    Hi Rory, thanks for reply. I hv tried severel things, but nothing has worked. Can you do it with example in attached sheets? I have to consolidate 6 sheets.
    Attached Files Attached Files
    Regards
    Prasad

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: pivot table (xp professional)

    You wrote that you have as much as 2 million rows. In Excel 2002 (XP) you'd need more than 30 sheets to store the data, not 6. That doesn't seem very practical. You should move the data into a single table in a database such as Microsoft Access or SQL Server.

    (Just as an illustration, I have attached your workbook with a pivot table based on multiple ranges. See Excel -- Pivot Tables -- Multiple Consolidation Ranges for a step-by-step description.)
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pivot table (xp professional)

    Thanks Hans, actually it is excel 2003 and limitation of rows are 65k. I have already done all these exercises. What I need is to get data as per "sample sheet" in attached workbook.
    Attached Files Attached Files
    Regards
    Prasad

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: pivot table (xp professional)

    Your sample sheet looks exactly the same as the raw data sheets! If that's what you want, then you will run out of rows for the pivot table just as you would for the raw data.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pivot table (xp professional)

    Hi Rory, I am not getting what you suggest. Actually names contains in colums A are common in all sheets. But I am not able to extract information as required. Pl help.
    Regards
    Prasad

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: pivot table (xp professional)

    My point was that looking at your sample seems to imply that you want to end up with one long list from all the raw data sheets - i.e. you want one line in the pivot table for each line in the raw data. If so, you will run out of rows. If not, can you clarify what the groupings are and whether you just want a straight sum of each of the values.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pivot table (xp professional)

    Actually, the data consisting sale of a particular product to different customers in a particular period and requirement is to get detail of one or all customer for whole period in given format. I am doing this with macros,which is very complexd and time taking also. Moreover, it is required to record macro each time a new customer added. The only problem is volume of data. Is there any simple way to do it?
    Regards
    Prasad

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: pivot table (xp professional)

    It sounds like you want to use a system that is able to handle the larger datasets directly. This suggests to me something more like Access (even if you had XL2007s larger worksheet size)....

    If you must keep it in excel in multiple sheets and want to extract some summary sheet, you will have to be specific about what you want the macro to do if you are asking for help in macro writing.

    But has been pointed out, if you want to display all the customers in one sheet with details, you will come upon the sheet limitations in XL pre-2007

    Steve

  14. #14
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pivot table (xp professional)

    Hi Steve, This is what I need exactly. can u write some simple macro to perform task?
    Regards
    Prasad

  15. #15
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='713701' date='31-May-2008 20:40']Hi Steve, This is what I need exactly. can u write some simple macro to perform task?[/quote]
    I am not sure whether I should re-open the topic but since it remain un-resolved(so far i am concerned), I take this liberty to do this. It took me almost a year or so (ofcourse I set the issue aside in between) to discover how to consolidate the multiple sheets in a single pivot table. Quote from the link provided by rory:

    "It can be done entirely within Excel. Go via menu data, import external data, new database query. Excel files. OK. Select your file. If you don't have defined names for the data the first time using the software you get a message about no named ranges found, OK past this and when you see options hit "system tables". This corresponds to worksheets in Excel. Select one worksheet (fields you want) and proceed until you get the option to go to MS Query. Then in MS Query hit the SQL button and amend the text from something like
    Code:
    SELECT field names
    FROM source
    to
    Code:
    SELECT field names
    FROM source
    UNION ALL
    SELECT field names
    FROM source_2
    and continue to add for each additional table "UNION ALL SELECT field names FROM source_n"

    After finishing the SQL hit the open door button (or use the file menu) to end MS Query and then take the pivot table option at the next dialog box."


    I have tested it with as much as 10 sheets having 30K Rows X 8Col each and its amazing.

    Yes, it can be done

    P.S. : This was my first post and reason alone to join the lounge.
    Regards
    Prasad

Page 1 of 3 123 LastLast

Posting Permissions

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