how to import and manipulate data from a report into a master sheet?

%3CLINGO-SUB%20id%3D%22lingo-sub-2837380%22%20slang%3D%22en-US%22%3Ehow%20to%20import%20and%20manipulate%20data%20from%20a%20report%20into%20a%20master%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2837380%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eproduction%20produces%20reports%20per%20team%20and%20we%20are%20looking%20to%20do%20some%20analysis%20with%20the%20data%2C%26nbsp%3B%3C%2FP%3E%3CP%3Ewhat%20i%20would%20like%20to%20do%20is%20get%20the%20report%2C%20add%20it%20as%20a%20sheet%20into%20a%20master%20excel%20where%20some%20formulas%20automatically%20pull%20in%20data%2C%20autofilling%20the%20blanks%20(according%20to%20station%20id)%20and%20deleting%2Fignoring%20the%20small%20tallied%20rows%20after%20each%20station.%26nbsp%3B%20but%20i%20cant%20get%20the%20report%20into%20a%20decent%20format%20without%20spending%20time%20on%20it.%20ideally%20id%20just%20want%20to%20name%20the%20sheet%2C%20and%20excel(or%20vba%20function%3F)%20do%20the%20rest%20in%20the%20least%20amount%20of%20clicks%20possible%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20have%20attached%20a%20sheet%20of%20the%20data%20format%20used%2C%20with%20the%20second%20sheet%20being%20my%20idea%20of%20a%20master%20so%20ican%20use%20a%20pivot%20table%2Fcharts%20to%20mess%20with%20data%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2837380%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2837813%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20import%20and%20manipulate%20data%20from%20a%20report%20into%20a%20master%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2837813%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1182992%22%20target%3D%22_blank%22%3E%40ju2k2%3C%2FA%3E%26nbsp%3BThis%20task%20is%20crying%20for%20PowerQuery%20(PQ)%20.%20Connect%20to%20the%20data%20in%20the%20first%20sheet.%20Remove%20the%20first%20row%2C%20Promote%20headers.%20Select%20first%20two%20columns%2C%20right-click%20one%20of%20the%20column%20headers%2C%20Fill%2C%20Down.%20Select%20third%20column.%20Press%20the%20filter%20button.%20Deselect%20%22null%22.%20Finally%2C%20set%20all%20columns%20to%20the%20correct%20data%20type.%20Close%20and%20load%20to%20a%20table%20(and%2For%20data%20model)%20or%20directly%20into%20a%20pivot%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20if%20you%20are%20not%20familiar%20with%20PQ%2C%20this%20all%20sound%20like%20too-much-to-handle.%20But%20the%20link%20below%20is%20a%20good%20start%20to%20learn%20more%20about%20it.%20Many%20of%20the%20steps%20(not%20all)%20mentioned%20above%20are%20explained%20in%20Chapter%207%20%22Basic%20transformations%22.%20It's%20not%20all%20that%20difficult%20and%20you'll%20love%20it!%20(Oh%2C%20not%20supported%20by%20Excel%20for%20the%20Mac.)%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceloffthegrid.com%2Fpower-query-introduction%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceloffthegrid.com%2Fpower-query-introduction%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2840800%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20import%20and%20manipulate%20data%20from%20a%20report%20into%20a%20master%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2840800%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1182992%22%20target%3D%22_blank%22%3E%40ju2k2%3C%2FA%3E%26nbsp%3BJust%20noted%20that%20you%20uploaded%20an%20XLS%20file.%20PQ%20requires%20XLSX%20files.%20Have%20attached%20a%20file%2C%20based%20on%20yours%2C%20including%20a%20PQ%20solution.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

 

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.