Excel -- identify organizational records from across 30 spreadsheets

[skydivetom]

Experts:

I need some general recommendations/feedback for processing a large data call across an organnization. Allow me to provide you some background first.

Below illustrates the **Data Collection** Process:
- We have an organization a) 30 division/branches and total of b) 1,000 employees.
- Spreadsheet template includes (header row + 1,000 rows for each employee).
- Also XLS includes locked-down columns for both "Division/Branch" name and "Employee" name.
- Also, beyond division/branch & employee name, XLS includes additional columns (with drop-downs) where data values will be selected/entered.
- Finally, XLS includes a command button which upon "click" will attach XLS to Outlook email and then send to data processing recipient(s).
- Then, we use XLS data and want to import into an Access database.

Although we will ultimately import data into Access, I am wondering if there can be some pre-processing that should be done in Excel. Here's what I need some help with...

... but again, some additional information first:

1. Once POC completed his/her information for all n employees in their division/branch, they'll click on the command "Send XLS Now" button.
2. In this case, the email recipient will receive (in an ideal world) all 30 XLS.
3. In and ideal scenario, I could import all 30,0000 rows (30 XLS times 1,000 employees). Then, in an ideal world, I could remove all blank rows from the 30,000 which would leave me with 1,000 completed rows.
4. However, there's a good chance that some rows/data will be left blank even though they should have received information.

My question: Whether in Excel (or Access), do you have a recommendation where I could readily identify the rows that below to the sender? All worksheets tab will have a generic name and XLS filenames may have been renamed so I cannot necessarily rely on cross-referencing "filename" = value in "Organization" column.

Any thoughts/recommendations on extracting the 1000 rows across 30,000 rows?

Thank you,
EEH

[MarvinP]

Hey Tom,

In the newer versions of Excel is Power Query that can import folders of information. If your data is structured all the same it might be great. I keep all my monthly bank statements in the same folder and import them as a group. If I add a new month I simply run the PQ again and the new month is added with ease. See if Power Query Folder fits a need in your toolbox.
https://support.office.com/en-us/art...8-6a00041c90e4

[skydivetom]

I'm not sure if I have Excel PowerQuery... in the event I don't, do you have an alternative solution/recommendation?