I want to be able to autogenerate a report in a specific format (See Final View.docx) from an excel sheet full of values (see sample Excel to be mapped.xlsx). Note that it doesn't necessarily need to be in DOC format. PDF, or even cleverly formatted XLSX, for example, would work.

I'm encountering two potential problems getting this to work:
1. Mapping the data to fields as formatted in Final View.docx[INDENT]
  • Fortunately, this is a relatively minor problem as I could just not export the data and keep everything native in Excel and export report by print PDF
2. Consolidating the different categories as formatted in Final View.docx
  • Unfortunately, this is more of a problem that I have no idea how to resolve; I'm hoping there some way to do it with a macro, but I have no idea how.
  • A possible solution is dead-mapping, assuming that the only values to be exported to a report are the ones listed in Excel to be mapped.xlsx. However, that is not the case. The mapping and consolidation needs to be fluid. If there were more items added (new and/or different Category, Date, Form, Details on new row, etc.) the consolidation and mapping needs to be done anew.

Type of report format desired (doesn't need to be exact): Final View.docx
Data to be used to populate the report format: Excel to be mapped.xlsx