Excel 2016 Pivot table or Consolidate or ?

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.