I’m aggregating a bunch of information collected routinely across a bunch of playing fields. The guys collecting this data have an Excel sheet which gets the data entered for each field, for each day, and to reduce mistakes the Excel sheet is set up exactly the same as the paper sheet, and I am not able to change any of this.
The upshot is that my source Excel files that I’ll be using are arranged as follows:
and I’ll need to take each of these individual cells (25 in each row) and make these columns, so each column will be named something like:
(Can only upload 1 image)
Column 1’s name then is a combination of Variable name one (Height of cut (warm season grass)) & Location (North goal) and Test Number (1), Column 2 is (Height of cut (warm season grass)) & Location (North goal) and Test Number (2) etc
This will be done for 27 separate variables (each having 25 observations - 5 locations X 5 tests, each of which needs an individual column) ie 675 columns.
If I was to use a macro of course I could reshape the data by copying and pasting, but this would mean having to run the macro and re-save the data every time a new sheet comes in, and over the next year or two, this will ramp up to about 150 fields with data coming in most days, so that’s untenable.
What I’d really like to be able to do is Import Files from folder, and have the query extract all these data so that each sheet will end up yielding just one row of data, albeit with 675 columns that then becomes aggregated for analysis and visualisation.
I guess one way would be to right click on individual cells, and drill down and save as a new query, and then append each of these (675) queries later, but that seems clumsy.
Before I go much further, I’d love to hear some alternates as I can’t be the first person to solve this problem I’m hoping.
Final wrinkle is that with each sheet, at the top there are 8 cells that also need to be added as columns - Field location, Field name, Irrigation, etc