+ Reply to Thread
Results 1 to 9 of 9

Creating pivot table from multiple ranges (sheets)

  1. #1
    Registered User
    Join Date
    02-19-2013
    Location
    Lithuania
    MS-Off Ver
    Excel 2003
    Posts
    12

    Creating pivot table from multiple ranges (sheets)

    Hello everyone,
    i have an issue while creating a pivot table.
    My excel file consists of 30 sheets where there are a table in each of them. I need to create a pivot from multiple ranges (i am using alt+d+p), but when it is created from multiple sheets, data gets scrambled. What could i be doing wrong?

    Please find an example attached. sheet "Merged data" contains information from three separate sheets, from that i can create the pivot i need, which is in "Correct pivot" sheet. Sheet "Incorrect pivot" contains table that is created using alt+d+p, from multiple ranges.

    I would really appreciate your help, thank you.
    Attached Files Attached Files

  2. #2
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Creating pivot table from multiple ranges (sheets)

    Hi Kestis,

    I'm not familiar with alt+d+p, can you explain the steps you take to create the pivots using this method.

    Is this used to select separate Ranges to be included in a Pivot Table?
    Remember you are unique, like everyone else

  3. #3
    Registered User
    Join Date
    02-19-2013
    Location
    Lithuania
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Creating pivot table from multiple ranges (sheets)

    Hi,
    i have found this tutorial and followed the instructions:

    http://www.contextures.com/xlPivot08.html

    Is there any other way i could achieve creating the pivot i need in some other way? I necessarily need to take data from multiple pages, as there is no other way.

  4. #4
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Creating pivot table from multiple ranges (sheets)

    Hi Kestis,

    I think f you don't have any other option than to use the files you get on separate worksheets,
    then you are probably using the right way to do it.

    I was able to replicate what you put on the 'Correct Pivot' worksheet (as near as possible).

    Have a look at the attachment.

    Cheers
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-19-2013
    Location
    Lithuania
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Creating pivot table from multiple ranges (sheets)

    Hi noboffinme,
    thank you, but I am still missing some functionality which comes when all data is in one table.
    Maybe you have suggestions how to achieve this?
    As i am facing the problem that my file has more rows than excel can handle per one sheet, that's why I am trying to create pivot from multiple sheets.

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Creating pivot table from multiple ranges (sheets)

    Hi,

    Do you have Power Query- also called Get and Transform in 2016- available to you?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  7. #7
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Creating pivot table from multiple ranges (sheets)

    Hi Kestis,

    What functionality do you mean?

    Also are you saying that you have more than 1 million rows made up of Pivot tables?

    That would be very unusual, can you clarify, Thanks

  8. #8
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Creating pivot table from multiple ranges (sheets)

    Hi Kestis,

    What functionality do you mean?

    Also are you saying that you have more than 1 million rows made up of Pivot tables?

    That would be very unusual, can you clarify, Thanks

  9. #9
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: Creating pivot table from multiple ranges (sheets)

    Hello,
    Your version seems to me that unless a solution using SQL will be good. Check out this guide and adjust to their data
    https://www.youtube.com/watch?v=EAf6Lhmjh-c

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Creating Pivot Table from Multiple Sheets
    By Cortlyn in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-15-2014, 06:17 PM
  2. Creating pivot table out of two ranges
    By Yoshi64 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-11-2014, 10:12 AM
  3. [SOLVED] Creating new Pivot table with dynamic sheet names/ranges
    By KeriM in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-08-2013, 08:54 AM
  4. Compiling multiple ranges of cells from multiple sheets into one grand table ?
    By teacher_rob in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-24-2012, 11:42 AM
  5. Replies: 3
    Last Post: 03-29-2009, 12:42 PM
  6. Pivot table Multiple consolidation of Ranges
    By prakash1731 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-26-2008, 02:16 PM
  7. [SOLVED] creating a pivot table w/ ranges from 2 worksheets
    By tl in forum Excel General
    Replies: 1
    Last Post: 08-20-2005, 03:05 PM
  8. [SOLVED] Pivot Table-Using Multiple SS's or Ranges?
    By Jugglertwo in forum Excel General
    Replies: 2
    Last Post: 02-05-2005, 01:06 AM

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