I was thinking more of 42 columns as in your original spec. 700 columns just adds to the complexity



However I am intrigued why you have the documents combined in the first place. What process do you have in place to create such a combined document?

On the face of it you appear to have a number of question sets combined into a single document. It would surely make things simpler if you were to have one question set per document, which makes it easier to extract the data by document to a separate line of the workbook (which is what I thought you were doing at the outset, when I suggested the add-in).

It might be possible to split the document into those separate documents, and then extract the data, but it would help to know both why you have it that way in the first place, and how the datasets are separated in the document so that they can be logically split into separate documents each with the same field sets.

__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com

It’s a long story, Graham. I work for a major educational testing company, developing all sorts of standardized tests, often consisting of multiple-choice questions (which we in the industry call “items”). For reasons all their own, a particular client wants batches of items delivered in an Excel sheet, one item per row. But the spreadsheet format (with items horizontally arrayed) is not at all congenial to the test development process. For that you need to have the items vertically arrayed in more or less the same way they’ll appear on the test and in a form that allows easy review and editing, and you want to have multiple items gathered into one document so that you can move readily from item to the next during the review process, resequence the items if necessary, and easily send all the items together from place to another. So for item development purposes, a Word file containing multiple items seems the most convenient option. The challenge I’m facing is to find the most convenient way to convert such a file to Excel format. You and others here have been a tremendous help. Thanks!