exceloffice-2013

Combine Multiple Tables Rows Into Master Table


Happy Monday Everyone!

Have a question and hope you can help. I have a budget spreadsheet that has a budget tab. On this tab is about 8 tables broken down into different categories. Every table in the tab has the exact same columns. Is there a non-vbscript/marco way to create a master table that combines all of the tables into a single table in a different tab. This seems like it would be a no brainer but I have tried everything I can think of and find online and there doesn't seem to be a decent solution without an addon called power query.


Solution

  • You can use the functionality of the pivottable wizard to consolidate multiple ranges (which are your tables) together into one pivottable.

    When it prompts for you to add your ranges use the table names with the following syntax: Table4[#All]

    You need the [#All] to get all the data associated with the table. Just repeat this for each of your tables names you want to consolidate.

    Full description i have given in my answer here:

    combining data from two sheets and generating pivot table in another sheet

    Note: If you want to keep the original table names or table numbers you will need to select the option:

    1) "I will create the Page Fields"

    2) Enter the Ranges using the table name e.g. Table4[#All]

    3) Select how many page fields do you want 1-4 and add item label used to identify the selected ranges below e.g. Table4.

    I am not sure if 4 items is the maximum or if this can be extended through VBA. However you can also use PowerQuery or UnionQuery.

    The following quotes are from here: http://www.contextures.com/xlPivot08.html

    I include some outline in case links are lost.

    PowerQuery:

    If you have a version of Excel that supports Microsoft's Power Query add-in, you can use it to combine the data in two or more tables. The tables can be in the same workbook, or in different files.

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

    Union Query:

    If you can't combine your data on a single worksheet, another solution is to create named ranges in an Excel file, and use Microsoft Query (MS Query) to combine the data.

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