+ Reply to Thread
Results 1 to 9 of 9

How to combine multiple tables into a Pivot Table?

  1. #1
    Forum Contributor
    Join Date
    07-06-2021
    Location
    Idaho
    MS-Off Ver
    2016
    Posts
    116

    How to combine multiple tables into a Pivot Table?

    I'm trying to combine multiple tables (1 in each tab) from the same worksheet.

    When I do, it's tethering the names from 1 list under the other so it look like the following

    Baba Ali
    - James Acaster*
    - James Adomian*
    - Jayde Adams
    - John Aboud*
    - Pamela Adlon*
    - Raymond Ablack*
    - Rose Abdoo*
    Ben Bailey
    - James Acaster*
    - James Adomian*
    - Jayde Adams
    - John Aboud*
    - Pamela Adlon*
    - Raymond Ablack*
    - Rose Abdoo*

    Instead of not duplicating the entries and listing all the names into a single column.
    Attached Files Attached Files
    Last edited by kyber; 10-13-2021 at 04:18 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-05-2013
    Location
    Singapore/China
    MS-Off Ver
    H&B 2016
    Posts
    1,138

    Re: How to combine multiple tables into a Pivot Table?

    Use Power Query instead to combine the 2 tables.

    M Code:
    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    848

    Re: How to combine multiple tables into a Pivot Table?

    @kyber;

    Since you tried using Pivot Table for listing unique counselors, i used the built-in Pivot Table Wizard function to solve the problem in sheet "Merged" of the attached file.

    Do not forget to refresh the pivot table.

    Note: If you only need the list of the counselors, the uncheck "Value" in the "Choose Fields" section in the "Field List"
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Haluk; 10-09-2021 at 03:48 AM.

  4. #4
    Forum Contributor
    Join Date
    07-06-2021
    Location
    Idaho
    MS-Off Ver
    2016
    Posts
    116

    Re: How to combine multiple tables into a Pivot Table?

    @Haluk this is exactly what I'm looking for, however, I'm a bit confused on where the data source of the Pivot Table is? And I have no idea how to merge the two.

  5. #5
    Forum Contributor
    Join Date
    07-06-2021
    Location
    Idaho
    MS-Off Ver
    2016
    Posts
    116

    Re: How to combine multiple tables into a Pivot Table?

    @josephteh I have quickly learned how to do the power query, however, to do this for 20 individual locations is a bit labor intensive so I was hoping there might be an easier way.

    Or perhaps if I can merge the tables by means of using an array formula that might do the trick too, but I'm not capable of that. I am struggling importing 1 table yet alone two. Here is what I have so far.

    =IFERROR(INDEX(Table1[COUNSELOR],MATCH(0,COUNTIF($A2:A$2,Table1[COUNSELOR]&"")+IF(Table1[COUNSELOR]="",1,0),0)),"")

    By merging it once for each location it will auto import every time I need to do a monthly update.

  6. #6
    Forum Contributor
    Join Date
    07-06-2021
    Location
    Idaho
    MS-Off Ver
    2016
    Posts
    116

    Re: How to combine multiple tables into a Pivot Table?

    @josephteh disregard my inquiry. I just learned that you can manually update power queries. My pivot table is now functional. Thank you.

  7. #7
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    848

    Re: How to combine multiple tables into a Pivot Table?

    @kyber, i've prepared an animation for you showing how to activate the Pivot Table Wizard and solve your problem.

    https://drive.google.com/file/d/1hdb...ew?usp=sharing

  8. #8
    Valued Forum Contributor
    Join Date
    01-05-2013
    Location
    Singapore/China
    MS-Off Ver
    H&B 2016
    Posts
    1,138

    Re: How to combine multiple tables into a Pivot Table?

    Glad you sort it out, kyber! Do try out the PivotTable Wizard suggested by Haluk. Instead of selecting the ranges, you could type in the Table Names.

  9. #9
    Forum Contributor
    Join Date
    07-06-2021
    Location
    Idaho
    MS-Off Ver
    2016
    Posts
    116

    Re: How to combine multiple tables into a Pivot Table?

    Thank you both!

+ 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. [SOLVED] Combine multiple tables using pivot table
    By SiStr in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 08-04-2021, 05:29 AM
  2. Combine 2 tables into one pivot table
    By MassiveExcelMan in forum Excel General
    Replies: 1
    Last Post: 04-11-2021, 12:33 PM
  3. Combine multiple tables into one pivot
    By KeighlR in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-07-2018, 10:36 AM
  4. Power Pivot to Combine Multiple Pivot Tables
    By chrapm1 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-07-2018, 04:20 PM
  5. Combine multiple pivots tables into one pivot chart
    By obrien918 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-13-2017, 04:29 PM
  6. How can I combine multiple tables into one pivot table?
    By crsport3 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-21-2013, 02:53 PM
  7. Replies: 1
    Last Post: 12-15-2009, 10:03 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