Forum Discussion
how to import and manipulate data from a report into a master sheet?
Hi all,
production produces reports per team and we are looking to do some analysis with the data,
what i would like to do is get the report, add it as a sheet into a master excel where some formulas automatically pull in data, autofilling the blanks (according to station id) and deleting/ignoring the small tallied rows after each station. but i cant get the report into a decent format without spending time on it. ideally id just want to name the sheet, and excel(or vba function?) do the rest in the least amount of clicks possible
i have attached a sheet of the data format used, with the second sheet being my idea of a master so ican use a pivot table/charts to mess with data
Thanks for any help
3 Replies
ju2k2 This task is crying for PowerQuery (PQ) . Connect to the data in the first sheet. Remove the first row, Promote headers. Select first two columns, right-click one of the column headers, Fill, Down. Select third column. Press the filter button. Deselect "null". Finally, set all columns to the correct data type. Close and load to a table (and/or data model) or directly into a pivot table.
Now, if you are not familiar with PQ, this all sound like too-much-to-handle. But the link below is a good start to learn more about it. Many of the steps (not all) mentioned above are explained in Chapter 7 "Basic transformations". It's not all that difficult and you'll love it! (Oh, not supported by Excel for the Mac.)
https://exceloffthegrid.com/power-query-introduction/
Riny_van_Eekelen thanks for the help, I don't know what power query is but the site you linked looks like a good start, and doesn't seem overly complicated. I'll have a look and give it a try,
Cheers
ju2k2 Just noted that you uploaded an XLS file. PQ requires XLSX files. Have attached a file, based on yours, including a PQ solution.
