Excel 2016 Pivot table or Consolidate or ?

[Briankf]

I have a large workbook with 77 tables of data. Each table is a list of parts that go into an assembly. The tables are very similar (Same headings and same number of columns) but with slightly different parts for each assembly and a few more rows in some than others. I need to consolidate the data from these tables when an assembly is purchased from us. For example, there could be 1 of the assembly on Table 1, 3 of assembly on Table 7, etc. I need to know which part we will need to order to make up those assemblies.

Should I use a Pivot Table or a Power Query or Consolidate or what's the best way to accomplish this?

[#2]

@Briankf 

If I were doing it, I'd approach it as I'll describe below. One of the lovely things about Excel is that there are always multiple ways to accomplish an objective. Fortunately you've done a pretty good job of describing your objective. I fully expect that somebody is going to come along and give you a Power Query solution. I'm old school enough (which is to say, I learned Excel before Power Query came along, and haven't yet successfully mastered it)....but, with that as the up front disclaimer

I'm a big believer in using a single table (aka database) whenever that can work. Excel is remarkably powerful when we need to extract data from that table. So, in your case, given that each of your 77 sheets is arrayed similarly (I think identically, except for numbers of rows)...

1. I'd make them all one big database, simply adding a column that differentiates which assembly a given row refers to. (Doing this would still allow you to readily create any one of the 77 distinctive lists by using FILTER and simply setting the criterion to the desired assembly descriptor.)
2. And then the Pivot Table would work to do that consolidation you're asking for.

I am assuming, since you've clearly been doing this kind of thing and asked about it, that you're proficient enough with Pivot Tables to know how to use its features to define/filter/display the kinds of results you're looking for.

So that's this man's way to tackle your task. Now I'm going to watch to see what other solutions come forth.

[#3]

@Briankf , Power Query can help with this really easily. See attached example with made up data. Here's the M-Code:

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Input")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}),
    #"Expanded Content" = Table.ExpandTableColumn(#"Removed Other Columns", "Content", {"ID", "Make", "Model", "Year"}, {"ID", "Make", "Model", "Year"})
in
    #"Expanded Content"

The Excel.CurrentWorkbook() function will give you the list of all tables in the current workbook. You just need to filter the ones you need to keep (I kept the ones that start with the "Input" in the example) and expand the tables to get your consolidated table. 

Perhaps this is the way to consolidate all the data into one as @mathetes  points out and then use it as your source for further analysis. I'm still learning the nuances of Power Query and trust in the wisdom of those that have done Excel modeling much longer than I have. Hope this is along the lines of what you are looking for.