Forum Discussion
Consolidation question
How do I consolidate and analyse data from five sheets, all with the heading project but with some entries missing on some sheets? The spreadsheet has 215 rows in total but when I consolidate with mi...
Okay, so to clarify, sheet one has the headings project, start date, end date (215 lines); sheet two has the headings project and project manager (206 lines - so some project number are missing); sheet three has the headings project and budget (206 lines); and sheet four has the headings project and funding source. I would like one consolidated spreadsheet with six columns (project, start date, end date, project manager, budget and funding source but some project numbers are missing. When this happens I'd like the missing cell to be left empty rather than the project lines becoming uneven/mixed up. Is this possible?
There are two obvious routes. The first is to use Power Query and Join the Tables by the Project. This will refresh on demand. The other option is to use a lookup formula
= XLOOKUP(Table1[Project],Table2[Project],Table2[Manager],"")This calculates immediately any change is made to one of the input Tables.
et Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Start", type date}, {"End", type date}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Project"}, Table2, {"Project"}, "Table2", JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Manager"}, {"Table2.Manager"}) in #"Expanded Table2"- Thanks Peter