I have a table in a word document and I can pull data from it into a new excel sheet.


I have multiple word documents from staff members of different faculties all stored in a single folder. I want to read those in and collate each faculties data on a single workbook on different sheets.

In the data I have a Faculty field and I want to collate all the same faculty answers in a single sheet. e.g. all the answers from Math faculty on Sheet named Math, all responses from English faculty on sheet named English etc.

I've worked out how to read the table from the word and I'm sure i can get to reading multiple word docs from a single folder, I've just no idea how to take the data and split it onto faculty based sheets.