Excel document needs auto-indenting based upon header/footer keywords

Attached is an Excel document, Excel_EqualWidthCells.Xlsx that has two sheets, one labeled "Structure".
The other is labeled, "Header-Footers", and looks like this:
Headers	Footers	 -- There is inconsistent use of "…", so assume that "…Begin" and "Begin" are keywords
…Begin	…End	 -- Notice the "…" acts like it is a singe char
…If		         -- Assume case insensitivity, so "BEGIN" and "begin" are also matches
…For		

Open in new window

For simplicity, I can remove the "…" from the document. Other tables have different keywords for their headers/footers, so I'll just change the corresponding headers and footer keywords in the VBA code.

The attached Excel_EqualWidthCells---ManualInden.xlsx document shows the desired result after the indentation macro is run.

The Excel_EqualWidthCells.xlsx file is very flat looking, yet describes a complex structure using headers and footers. I manually indented sections of rows so that I could see the structure. This is tedious, but doable.

One suggested approach in a different question is found here.
https://www.experts-exchange.com/questions/29131489/Use-keywords-and-expressions-to-indent-nested-structures-in-Excel.html#a42769597
"This can be done with a stack structure, where you push a set of items on the stack when some condition is detected and remove them when another condition is detected.  In your case the first set of conditions is a cell that begins with ("loop", "if") and the second set of conditions is a cell that begins with ("end").  If you're working in Excel, you might use the stack 'level' (depth) as a parameter in an .Offset() method.

"With as simple a problem as this one, your stack structure can be an integer variable.  You might indent as you iterate the rows."

I am hoping that a macro can look at the headers, and when a footer is found, then it is associated with the previous header row number at the top of the stack. Having the two row numbers, the macro can indent the rows one column to the right (and then pop the stack to throw away that header information.)

This question is much simpler than a similar question because the excel spreadsheet has no merged vertical cells. I am told this makes indentation much easier to do.

CAVEAT: The headers have horizontal merged cells, which sometimes required me to move the header to an open area, then indent the rest of the range, and then move the header back to the correct position. This only occurs if there are two or more headers in a consecutive rows. Then the inner header needs to be moved to a cell that is merged, and when I was unable to do this directly. But other one line "indent" macros (in other questions) seemed to have no trouble moving a merged horizontal header or footer row to the next column.

Attached is the Excel_EqualWidthCells---ManualInden.xlsx, which is the result of my manual, no-macro, indentation. When the macro-driven solution is done, it should be easy to compare results to see whether I made an indentation mistake or not.
Excel_EqualWidthCells.xlsx
Excel_EqualWidthCells---ManualInden.xlsx
LVL 33
phoffric\Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jan Karel PieterseExcel and VBA ExpertCommented:
Like so:
Sub IndentAll()
    Dim lIndentLevel As Long
    Dim oCell As Range
    Dim sVal As String
    For Each oCell In Worksheets(1).UsedRange.Columns(1).Cells
        sVal = LCase(CStr(oCell.Value))
        If sVal Like "?begin*" Or sVal Like "?if *" Then
            lIndentLevel = lIndentLevel + 1
        ElseIf sVal Like "?end*" Then
            lIndentLevel = lIndentLevel - 1
        Else
        End If
        If lIndentLevel < 0 Then lIndentLevel = 0
        If lIndentLevel > 0 Then
            oCell.Resize(, lIndentLevel).Insert xlToRight
        End If
    Next
End Sub

Open in new window

Martin LissOlder than dirtCommented:
The … acts as one character because it is chr(133) , Horizontal ellipsis, rather than chr(46) ,Period/full stop. I will assume that you are not going to change them.
phoffric\Author Commented:
@Jan,
Thank you. Even though you don't go with the Header-Footers sheet, I like your idea of not even requiring a stack. :)
But, for some reason, when I run the macro, it doesn't indent as shown in the attached result. Sometimes, the header and corresponding footer are not on the same column. Sometimes a header row gets indented when it should. Sometimes, the body between header and footer do not get indented. Sometimes, it seems to partially work.


Being new to Excel/macros, maybe I didn't follow proper operating procedures.
Did you test the macro and found it to work and compares well with the attached Excel_EqualWidthCells---ManualInden.xlsx file? If so, maybe you could attach the unindented file with the macro included, and let me run the macro to see if it works.

I tried modifying your code as follows, but I still get indentation mistakes:
Sub IndentAll()
    Dim lIndentLevel As Long
    Dim oCell As Range
    Dim sVal As String
    For Each oCell In Worksheets(1).UsedRange.Columns(1).Cells
        sVal = LCase(CStr(oCell.Value))
        If sVal Like "?begin*" Or sVal Like "?if *"  Or sVal Like "begin*" Or sVal Like "if *"  Then
            lIndentLevel = lIndentLevel + 1
        ElseIf sVal Like "?end*" Or sVal Like "end*" Then
            lIndentLevel = lIndentLevel - 1
        Else
        End If
        If lIndentLevel < 0 Then lIndentLevel = 0
        If lIndentLevel > 0 Then
            oCell.Resize(, lIndentLevel).Insert xlToRight
        End If
    Next
End Sub

Open in new window


When I try to save, the popup says cannot save to macro-free workbook. But I hit "Yes" I want to save anyway. But when I re-open the workbook, I don't see the macro.

Thanks,
Paul
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

phoffric\Author Commented:
I found "… " in the original spreadsheet. I will change it to be "…" (i.e., without the space).

>> I will assume that you are not going to change them.
I understand. Since I found that it is easy to do a global replace removing them, I am perfectly happy to eliminate the "…" from the spreadsheet. I want to make it easy.

Thanks again,
Paul
Martin LissOlder than dirtCommented:
What "end" should be paired with "…Begin repeated for EVENT_SETS"?
phoffric\Author Commented:
>> What "end" should be paired with "…Begin repeated for EVENT_SETS"?
It is not necessary for the algorithm to use any words following the header or footer keyword.
But according to my attached Excel_EqualWidthCells---ManualInden.xlsx, the "Begin repeated for EVENT_SETS" on line 4 has its matching end footer on line 94. When the stack algorithm reaches line 94 and sees a footer, then it looks at the stack, and pops out a line 4. The the lines 5 to 93 are indented one row to the right.

The stack algorithm (not Jan's approach) simply puts header rows onto the stack (ignoring the type of header and all words following). Here is the algorithm that I wrote in another related (but more complicated question):
Scanning the table:
Upon seeing a header keyword, push the row number onto the stack.
Upon seeing a footer keyword:
1. record the row number of the footer.
2. Extract the row number at the top of the stack
3. Pop the stack
4. Indent from (Header row # + 1) to (footer row # -1)

Open in new window


I am relaxing the idea of having a list of header/footer keywords in a separate sheet to make it easier. This is so provided that I can easily add/modify/delete the keywords as I have been able to do using Jan's VBA at lines 7 and 9. As I wrote, although I can create/run a macro on the fly, I don't know how to properly save it; but I'm sure that there is a simple explanation.
phoffric\Author Commented:
Jan's approach seems easier on the surface than using the stack, since it doesn't appear to require a stack. But, it probably needs a little adjustment.
phoffric\Author Commented:
Just wanted to remind you of the OP statement where I had to take special action manually:

"CAVEAT: The headers have horizontal merged cells, which sometimes required me to move the header to an open area, then indent the rest of the range, and then move the header back to the correct position. This only occurs if there are two headers in a consecutive rows. Then the inner header needs to be moved to a cell that is merged, and when I do this manually, excel complains."
Martin LissOlder than dirtCommented:
If you are saying that the rows between 4 and 94 should be indented, then I believe your sample is wrong because in that sample rows 34 to 36 are not indented.
phoffric\Author Commented:
Looking at Excel_EqualWidthCells---ManualInden.xlsx, I looked at a sampling of rows, and their indentation looks good to me. Not sure why you say 34 to 36 are not indented.

01-04: not indented
05-07: indented 1 col

32-33: indented 4 col
34-36: indented 5 col
37-37: indented 6 col
38-38: indented 5 col
39-39: indented 4 col
40-42: indented 3 col

90-91: indented 3 col
92-92: indented 2 col
93-93: indented 1 col
94-96: not indented
Martin LissOlder than dirtCommented:
I don't believe that any approach that simply puts "End"s in the stack will work because there still must be some way to determine which header goes with which footer. For example with this structure...

header1
dataA
header2
dataB
footer2
dataC
header3
dataD
footer3
footer1

how will the code know that
  1. That dataA to footer3 should be indented, then
  2. dataB should be indented, then
  3. dataD should be indented
Martin LissOlder than dirtCommented:
This is from the workbook you uploaded.
2019-01-14_15-22-18.png
Martin LissOlder than dirtCommented:
In my example please assume that all headers have …If in front of them and all footers have …End
phoffric\Author Commented:
I'll look at your latest post, but I am still unclear what you found wrong with rows 34-36.

>> assume that all headers have …If in front of them and all footers have …End
OK, I will add the … if missing - np.
Martin LissOlder than dirtCommented:
still unclear what you found wrong with rows 34-36.
They start in column 'A' while, if I understand the intent (and I'm starting the think I don't) rows 4-94 should all start in column 'B'.
phoffric\Author Commented:
re:  rows 34-36
>> They start in column 'A'
In attached document, Excel_EqualWidthCells---ManualInden.xlsx, hardly any rows start in column A; and columns 34-46 start in col F.
phoffric\Author Commented:
In following post is a proposed scenario.
https://www.experts-exchange.com/questions/29131489/Use-keywords-and-expressions-to-indent-nested-structures-in-Excel.html#a42771531

In following post, I attempted to show how an footer is matched with its corresponding header using a stack:
https://www.experts-exchange.com/questions/29131489/Use-keywords-and-expressions-to-indent-nested-structures-in-Excel.html#a42771580

I am hoping that Jan's approach will work with a tweak, because, at the moment, it looks simpler than a stack approach.


>> rows 4-94 should all start in column 'B'.
The attached final product spreadsheet shows the intent.
Martin LissOlder than dirtCommented:
I'm sorry, but I've spent 20+ hours on this and I don't think it's possible, so I'm giving up. I'll be happy if someone else comes up with a solution because then I'll learn something.
phoffric\Author Commented:
If it is not possible, then I will mark your post as the solution.

Sorry you spent so much time on this. Thanks for giving this a shot. Sorry that I may not have been clearer on the intent. I spent a lot of time manually indenting so that I could show the Before and After versions in the OP. From the other questions, I thought that you understood that the level of indenting would increase with every header found.

I just looked this up. Maybe this vba code will help you understand what I was referring to with the stack solution. But again, I am hoping that Jan's approach will turn out good. His approach seems very viable.
https://stackoverflow.com/questions/4871485/lifo-stack-algorithm-class-for-excel-vba

Here is a very simple stack class.
Option Explicit
Dim pStack As Collection
Public Function Pop() As Variant
    With pStack
        If .Count > 0 Then
            Pop = .Item(.Count)
            .Remove .Count
        End If
    End With
End Function
Public Function Push(newItem As Variant) As Variant
    With pStack
        .Add newItem
        Push = .Item(.Count)
    End With

End Function
Public Sub init()
    Set pStack = New Collection
End Sub

Open in new window

Test it
Option Explicit
Sub test()
    Dim cs As New cStack
    Dim i As Long
    Set cs = New cStack
    With cs
        .init

        For i = 1 To 10
            Debug.Print CStr(.Push(i))
        Next i

        For i = 1 To 10
            Debug.Print CStr(.Pop)
        Next i
    End With
End Sub

Open in new window

Martin LissOlder than dirtCommented:
If it is not possible, then I will mark your post as the solution.
I don't see where you've done that, but that's OK since I didn't supply a solution. I think I should mention that as tedious as it may be, the workbook I developed where you manually choose headers and footers should work.
phoffric\Author Commented:
>> If it is not possible
>> I don't see where you've done that
I haven't done it yet since I have not given up yet. And there is still a potential solution with Jan's post.
Martin LissOlder than dirtCommented:
Thinking about the tediousness of that manual header/footer matching workbook, would this help? It lists all the headers and footers (with the footers bottom up, and you would need to match them manually. I've done that for rows 4 and 94.
2019-01-14_23-10-46.png
Jan Karel PieterseExcel and VBA ExpertCommented:
Looks like this little routine does produce ALMOST identical results as your example:
Option Explicit

Sub IndentAll()
    Dim lIndentLevel As Long
    Dim oCell As Range
    Dim sVal As String
    For Each oCell In Worksheets(1).UsedRange.Columns(1).Cells
        sVal = LCase(CStr(oCell.Value))
        If sVal Like "?end*" Or sVal Like "???end*" Or sVal Like "end*" Then
            lIndentLevel = lIndentLevel - 1
        End If
        If lIndentLevel < 0 Then lIndentLevel = 0
        If lIndentLevel > 0 Then
            oCell.Resize(, lIndentLevel).Insert xlToRight
        End If
        If (sVal Like "?begin*" Or sVal Like "begin*" Or sVal Like "???begin*" _
            Or sVal Like "?if *" Or sVal Like "if *" Or sVal Like "???if *" _
            Or sVal Like "?for *" Or sVal Like "for *" Or sVal Like "???for *") Then
            lIndentLevel = lIndentLevel + 1
        End If
    Next
End Sub

Open in new window

The exceptions appear to be cases where the three dots are followed by a space character, if I first remove the spaces after all three dots, my routine produces the same as your example output.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Excellent code Jan!
phoffric\Author Commented:
I agree. Excellent code Jan! Perfection achieved!! :)
Kudos to you for finding an even simpler method than using the stack approach.

Yes, your macro produced ALMOST identical results as in my example. There were two lines that were different. In both cases, I accidentally indented by an extra column. Surprised they were the only two mistakes I made.

BTW, as you may have noticed, there were a number of "||" and " .. " in the cells. I added these to denote separate line breaks and paragraph markers that I replaced in the Word document before copying to Excel. Doing this assured me of having no vertical cell merging resulting in one row having one attribute in Column A.

In this question, because there were still line breaks, the Excel doc had vertical merged cells, which apparently added some complexity to the indentation. Do you happen to have a way to modify your macro to handle the vertical merged cells? If so, here is the question:
https://www.experts-exchange.com/questions/29130882/Excel-Spreadsheet-Error-when-moving-rows-one-column-to-the-right.html

Thanks again!
Paul
phoffric\Author Commented:
Excellent code Jan! Perfection achieved!! :)
Kudos to you for finding an even simpler method than using the stack approach.

Yes, your macro produced ALMOST identical results as in my example. There were two lines that were different. In both cases, I accidentally indented by an extra column. Goes to show that sometimes programs are better than people.
Jan Karel PieterseExcel and VBA ExpertCommented:
To handle vertically merged cells a different aproach is needed. Haven't got time today I'm afraid.
phoffric\Author Commented:
@Jan,
No problem. That was my original indentation question. But I kept trying to simplify it in other questions until I found a good-enough solution.

Thanks again! I appreciate your assistance - very elegant!
Jan Karel PieterseExcel and VBA ExpertCommented:
You're welcome.
phoffric\Author Commented:
Hi Jan,
Just thought I'd say thanks again. I took your macro to my workstation and ran it on the large table I want to analyze. The results are beautiful.
Paul
Jan Karel PieterseExcel and VBA ExpertCommented:
That is nice to hear, thanks for letting me know.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.