It is important that the end result of the Excel worksheets looks very professional with regards to formatting and structure. Any lack thereof renders the software useless for my use case.
Data source are word documents of a couple of 100 pages in English language.
1) The content of these documents needs to be imported into Excel, so that each document is one Excel file, each sentence is one row in the main worksheet and I can collapse and extend the content of each of the headings.
2) The main workbook has several columns, that I can freely add, change and delete. But as a standard the columns and their possible values are
- Supplier obligation (y/n)
- Customer obligation (y/n)
- Deliverable (y/n)
- Milestone (y/n)
- Service Boundary (y/n)
- Ambiguous language (y/n)
- Operational Risk (High, Medium, Low)
- Service Level (y,n)
- Supplier obligation to report, provide notice (y/n)
- Surprising clause (y/n)
- Importance of clause (High, Medium, Low)
I have to be able to freely add, change or delete the columns and their possible values.
3) In order to automatically fill the columns for each row, I need a macro that searches the text of the workbook and automatically sets the respective value in the respective column, if it finds a certain word of a defined list of words or a defined phrase of a defined list of phrases.
4) The definition of the possible values for the columns as well as the definition of the lists of words or phrases has to happen in a separate "administration" worksheet (in the same file) in a very user friendly way, so that I don't need any particular Excel knowledge to define those.
I open the workbook creator file, point to a document or a number of documents, press "convert" and the workbook creator creates one Excel file per word document with each sentence being one row and in a way that I can collapse and extent per heading.
I go to the "administration" worksheet and I define the list of words and/or phrases that create a specific value for each of the possible values in each of the columns. E.g. I am defining that the phrase "Supplier shall" should create a "y" in the Supplier Deliverable column.
I press a RUN button and a macro fills the respective columns in accordance with my defined lists.
If I afterwards change the lists or values and RUN again, the content created by the previous RUN will be deleted and the new RUN writes all values new.
I can filter all columns.
8 freelancers are bidding on average $318 for this job
hey lutzzy, can you please share a sample word document and the populated excel sheet (1 is enough). we like to have a look on how it is populated. thank you :-)