Best way to automate result filtering?

Help- I'm just starting to learn about automation within Excel and I'm not sure what I even really need to be asking.

The short version is, I need to be able to dynamically sort out blank columns based on filter results for another column.

More specifically, one of my job dutirs is pulling and distributing customer satisfaction survey results across our system. Someone along the line decided the best way to do this was with a single, massive survey on Survey Monkey, with location based filters automatically giving the customers site specific questions. What this means for me is that every week I'm using views within the survey monkey analysis screen to pull results individually for each location, then unzipping and naming them, before finally being able to send them out to the various teams.

If I pull the entire survey with no views applied, there are a ton of columns, with each location having a certain block of columns containing results. E.g. location a results are in columns d-f, location b results are in columns g-i... Etc.

What I would like to do is pull the entire block of results and then use some sort of automation to sort results automatically, taking this from an hours long process down to a matter of minutes. Should I be looking at power query? Macros? Is this functionality built in to an existing tool, like pivot tables or something?

Any direction on where I should be going to learn more is appreciated.