Jul 31 2020 08:39 AM
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?
Jul 31 2020 09:23 AM
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)...
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.
Jul 31 2020 07:25 PM
@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.