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

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/