I'm wanting to make a messy process easy. We have several different database systems available for employees to use. They need to sort through their client database and send us only a few columns (i.e. Client Score, First Name, Last Name, Phone Number, etc.). I'm wondering if there is a way to find a column with a header, "Client Score," from a huge spreadsheet, and return the entire "Client Score" column on a new sheet. This would have to be done for each column and would need to work for different databases.
Shouldn't I be able to enter a function to look for the column header in a specified sheet, then return that entire column?
In brief Get & Transform in Excel 2016 (or Power Query in previous versions) looks like the right tool.
You connect to you raw table (in source Excel file, or in SQL databases,.. many other connectors), select the column(s) you need; remove all others and load result into your Excel sheet. Simplest variant of such query could be generated in few clicks, with each new source table only Refresh all is required (manually or programmatically).
For more concrete recommendations desirably to have more concrete information about data flow organization.