troubleshooting Question

Use keywords to indent nested structures in Excel

Avatar of phoffric
phoffric asked on
Microsoft OfficeMicrosoft ExcelOffice ProductivitySpreadsheetsVBA
96 Comments2 Solutions672 ViewsLast Modified:
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

To get an idea of the desired result, this link gives a similar example of pictures of the a sheet before indenting and after indenting.
https://www.experts-exchange.com/questions/29130882/Excel-Spreadsheet-Error-when-moving-rows-one-column-to-the-right.html#a42766586

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
loop
for
if


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:
1st header
data
2nd header
data
3rd header
4th header
data
4th footer
data
3rd footer
data
data
5th header
data
6th header
data
6th footer
5th footer
data
2nd footer
data
data
1st footer
data


After indentation (used EE indent tags):

1st header
data
2nd header
data
3rd header
4th header
data
4th footer
data
3rd footer
data
data
5th header
data
6th header
data
6th footer
5th footer
data
2nd footer
data
data
1st footer
data


Thanks in advance.
Paul
ASKER CERTIFIED SOLUTION
aikimark
Get vaccinated; Social distance; Wear a mask
Join our community to see this answer!
Unlock 2 Answers and 96 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 96 Comments.
Try for 7 days

”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