Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple consolidation ranges (2003(11.8012.8036) SP2)

    Hi there!

    I have been asked by a user to help out with combining two worksheets of data into a single Pivot Table. This isn't going too well.

    When performing the above Pivot I end up with the field headers in a Pivot Column and effectively can't do anything with it.

    The attached sample data workbook displays this (Multiple Consolidation Pivot Tab) and what I would like to achieve (All Synds Pivot Tab).

    The latter pivot has been created by combining the worksheets "Synd 994" and "Synd 2923" together and then performing the Pivot.

    Any thoughts and advice would be greatly appreciated. Incidentally the original worksheets have in excess of 40,000 records each.

    Cheers,

    Niven <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

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

    Re: Multiple consolidation ranges (2003(11.8012.8036) SP2)

    That's the way pivot tables from multiple consolidation ranges work.

    I'd import the individual worksheets into an Access (or SQL Server) database and create a union query to combine them. You can then create a pivot table in Excel based on the union query (no need to import the data back into Excel).

  3. #3
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple consolidation ranges (2003(11.8012.8036) SP2)

    Many thanks your reply.

    The project in question has quite a few other facets, such as multiple lookups and links across worksheets and my first suggestion was that Access would be the right way to go about what they were trying to achieve.

    The workbook is now 59Mb in size and caning itself and the network when recalcing.

    I think I'll leave them to it.

    Cheers, <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Niven

  4. #4
    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: Multiple consolidation ranges (2003(11.8012.8036) SP2)

    Hi,
    I would go with Hans' solution if you can (or base the pivot table directly on whatever db the data came out of, if you have the ODBC drivers), but you can actually do it with Excel too:
    1. Save a workbook with your 2 syndicate data sheets.
    2. In a new workbook, choose Data-Pivot table and Pivot chart report, select External Database as the source, choose Excel Files and browse to your workbook from step 1. If you get the message that there are no tables, click the Options button and check the show System tables option.
    3. Step through the Query wizard picking any fields until you get to the step where you can return data to Excel or Edit the query in MSQuery. Choose to edit the Query, press the SQL button and replace the text that is there with this:
    <code>SELECT `'S1'`.SYND, `'S1'`.CCY, `'S1'`.` YOA`, `'S1'`.MONTH, `'S1'`.`Month End`, `'S1'`.`CAT CODE`, `'S1'`.RCC, `'S1'`.`FSA GROUP Split`, `'S1'`.FIL, `'S1'`.FIL4, `'S1'`.BUSINESS, `'S1'`.TF, `'S1'`.TF2, `'S1'`.GEOG, `'S1'`.STATS, `'S1'`.`REP LEVEL`, `'S1'`.`RI CEDED`, `'S1'`.VALUE, `'S1'`.`UW REF`, `'S1'`.`Qual Cat`
    FROM `C:TestConsolidationPivotTest`.`'Synd 2923$'` `'S1'`
    UNION
    SELECT `'S2'`.SYND, `'S2'`.CCY, `'S2'`.` YOA`, `'S2'`.MONTH, `'S2'`.`Month End`, `'S2'`.`CAT CODE`, `'S2'`.RCC, `'S2'`.`FSA GROUP Split`, `'S2'`.FIL, `'S2'`.FIL4, `'S2'`.BUSINESS, `'S2'`.TF, `'S2'`.TF2, `'S2'`.GEOG, `'S2'`.STATS, `'S2'`.`REP LEVEL`, `'S2'`.`RI CEDED`, `'S2'`.VALUE, `'S2'`.`UW REF`, `'S2'`.`Qual Cat`
    FROM `C:TestConsolidationPivotTest`.`'Synd 994$'` `'S2'`</code>
    changing the path to the workbook as appropriate.
    4. Select File-Return data to Excel (ignore the message that the query can't be shown) and then step through the Pivot Table wizard as normal.

    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple consolidation ranges (2003(11.8012.8036) SP2)

    WOW!

    Now that was pretty impressive and very, very, useful. Just what I was looking for.

    Many thanks,

    Niven

  6. #6
    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: Multiple consolidation ranges (2003(11.8012.8036) SP2)

    I'd still smack them over the head and tell them to use Access for at least the raw data storage! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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