Write some Software

I have a number of excel documents that are all formatted the same way. I need to pull all the data into a single sheet while reorganizing the information. For example. each sheet has data in cell LOC1, LOC2, LOC3, LOC4. I need to have all the LOC1s from all the documents grouped together, then All the LOC2s together and so on. I would like to be able to run the script and it would generate a new document with the data formatted the same way as the attached document. (I can go into the script and edit the root path of the folder... that would be fine). All the documents will be in the same directory. The number of files may differ from one directory to the next, so the script must be iterative. Also, the script must be able to handle that one set of documents might have 3 rows of data for LOC1 while another set of documents might have 2 row for LOC1. i.e. the script must parse and iterate through the files, not be hardcoded to this exact format.