+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 19

Pivot table from two different worksheet whose labels partially match

  1. #1
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal (The country of Mount Everest)
    MS-Off Ver
    Excel 2003-2013
    Posts
    605

    Pivot table from two different worksheet whose labels partially match

    Hi,

    I have two worksheets.I want a single Pivot table to be created by combining this two worksheets.The label matches between these two worksheets partially.I am working in office 2013/16.Can you help me in this regard.

    Sample data workbook is enclosed for your kind reference.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016/Work 2013 Pro Plus
    Posts
    1,243

    Re: Pivot table from two different worksheet whose labels partially match

    If it was me I would be making sure that what you use as the primary key in each set of data matched exactly before trying to merge them.

  3. #3
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal (The country of Mount Everest)
    MS-Off Ver
    Excel 2003-2013
    Posts
    605

    Re: Pivot table from two different worksheet whose labels partially match

    I have not done anything as such that.Can you help me in the workbook as provided above.

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    Cool Re: Pivot table from two different worksheet whose labels partially match

    is that what you want?

    add more fields if necessary

    done with PowerQuery (Get&Transform) and PivotTable
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  5. #5
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal (The country of Mount Everest)
    MS-Off Ver
    Excel 2003-2013
    Posts
    605

    Re: Pivot table from two different worksheet whose labels partially match

    Hi,
    can you give me the screenshot in the excel itself so that I can know how you have performed.

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    Re: Pivot table from two different worksheet whose labels partially match

    open file with Ex2016, go to Data - Show Queries -dbl click on Append1 and you'll see all steps on the right side

  7. #7
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal (The country of Mount Everest)
    MS-Off Ver
    Excel 2003-2013
    Posts
    605

    Re: Pivot table from two different worksheet whose labels partially match

    I think I could not get the steps.I will be very much more thankful if you could elaborate in detail stepwise (if possible screenshot)as how you have done so that I can do in my original workbook which has a large data.

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    Re: Pivot table from two different worksheet whose labels partially match

    Quote Originally Posted by paradise2sr View Post
    I am working in office 2013/16.
    with Ex2016 (for Ex2013 you'll need PowerQuery add-in)
    1. select first table
    2. Data - From Table (it will add table to Workbook Queries)
    3. in PowerQuery window - Close & Load To - Only Create Connection
    4. the same with the second table
    5. Data - Show Queries
    6. Right click on the first table and select Append
    7. in new window first table will be choosen
    8. choose second table
    9. OK
    10. Close & Load To
    11. Only Create Connection
    12. Insert - Pivot Table
    13. Use External Data Source
    14. Choose connection
    15. Select Query-Append1
    16. OK
    I hope I did not miss anything

    btw. you didn't answer for my question - is that what you want? (result)
    Last edited by sandy666; 03-09-2018 at 10:31 AM.

  9. #9
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal (The country of Mount Everest)
    MS-Off Ver
    Excel 2003-2013
    Posts
    605

    Re: Pivot table from two different worksheet whose labels partially match

    Thanks for your kind reply.I face few problems:
    1.Close and load only to create connection was in disable form for 1st table.
    2.Query Append 1 created at the same time Query Append 1(2)also created.
    3.Date was shown in pivot table as Years, Quarter,Month in 3columns instead of Single date as dd/mm/yy format.

    Can you help me how to resolve these issues.
    Actually I am making two pivot table for reporting from two worksheets.By using one Pivot Table,it would be easy for analysis as both are interrelated.
    Last edited by paradise2sr; 03-09-2018 at 12:01 PM.

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    Re: Pivot table from two different worksheet whose labels partially match

    ad3.
    Ungroup

    ad.1&2 you need to wait a few...

  11. #11
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal (The country of Mount Everest)
    MS-Off Ver
    Excel 2003-2013
    Posts
    605

    Re: Pivot table from two different worksheet whose labels partially match

    ad3 I couldn't get into it.

  12. #12
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    Re: Pivot table from two different worksheet whose labels partially match

    Ok download this one and unzip: FILE

    post file with the problem

  13. #13
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal (The country of Mount Everest)
    MS-Off Ver
    Excel 2003-2013
    Posts
    605

    Re: Pivot table from two different worksheet whose labels partially match

    Sorry for the delay.I was out of the city.
    Ok I have tried your post #10 of ungrouping & it worked and query raised in Post #9 point no.1 & 3 has been resolved.Still point no.2 i.e Append1(2) is also created simultaneously.However the results are fine.
    In addition to it,I have changed heading of my table 1 but he same could not be reflected in pivot table.Is there some restriction.Kindly let me know.

    I could not be able to post in your desired location,since the file is too big 7MB and confidential.Hope you understand.
    Attached Images Attached Images

  14. #14
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    Re: Pivot table from two different worksheet whose labels partially match

    After load data from sheet to PQ you cannot change header(s) in source table. You can do that in Query Editor.
    Plan your work.
    or
    if you really want to change headers in source table, make change to the same name in appropriate column in PQ table with every step.



    If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
    then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
    If you did it already - ignore it.
    Thank you.

  15. #15
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal (The country of Mount Everest)
    MS-Off Ver
    Excel 2003-2013
    Posts
    605

    Re: Pivot table from two different worksheet whose labels partially match

    Thanx for your kind reply.If there is also alternative method which is quick without creating any helper sheet,pls also let me know.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

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