Attached is an example Excel spreadsheet with syntax similar to a table in a 60 page word doc. It represents a data structure that has many substructures in it that are controlled by loops and if statements. It is hard to see the structure, so I have learned a couple of techniques to try to indent sets of rows to the next column. For example, I now have a macro that will indent a manually selected set of rows one column to the right. Nested-labeled_Conditions.xlsm
If you have a solution, I'll add an extra nested structure to see if it works in general.
<<EDIT 2019-01-16 12:40am EST>>
I changed the title in an important way. No longer should the Headers and Footers be considered as expressions. I now can see that expressions are much too difficult to maintain and vary immensely from table to table as there are potentially many different authors with their descriptive writing style.
The Headers and Footers should be considered as a single known word(s) - and it will be the first word in Column A. Currently, it is safe to say that the Footer will be always: End - but test should be case insensitive, so end also is a footer.
Some headers I have seen in general are as follows (not all might be used in attached sample spreadsheet): begin
Luckily, I have not seen else, so don't worry about that. If I find other keywords, then I should be able to modify the macro's test condition.
The special ellipses char (i.e., 3 dots) before a keyword should be ignored as I can easily remove them from Excel by replacing them. (And feel free to remove them.)
One major problem is that there are vertical merged cells which possibly can result in data loss when indenting, and losing text is undesirable. Also, a potentially minor problem is that the headers and footers are usually merged horizontal cells. For a given field in Column A, that record may have in the actual spreadsheet up to 7 vertical merged cells in different columns in Column A; and in the field's record, other columns might have no merged cells, or another column could have a different number and organization of merged cells. Crazy, right? What is known is that if a field name has no merged cells in Column A, then there will be no merged cells in the other columns in the record.
Here is a schematic drawing of a sheet before indenting:
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
-Mike Kapnisakis, Warner Bros
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.