Great tool for data room indexes
The other day at the office, I had an interesting request. An attorney had an Excel index from a data room, and wanted to use Excel’s grouping feature to represent the file/folder structure.
The data room, as is typical, had an index column that used a string concatenated from integers to represent file and folder location. For instance, “1,” “2,” “3” etc. are the top-level folders. “1.1” may be a subfolder or a file under folder 1, etc.:
What the attorney wanted was to have the grouping “plus signs” at each folder level, grouping the files and subfolders below that folder.
I came up with the solution using VBA code and one helpful setting in Excel. First, the setting. Excel by default places the summary rows (i.e., the plus signs) below the detail, or group. In this application, we want the plus signs above the group. To allow this, click the dialog launcher under Data > Outline and uncheck “Summary rows below detail.” This is a worksheet-level setting, so it will stick with your sheet.
Another issue to be aware of: Your index column should be formatted as text, so that, for instance, “1.20” does not come into Excel as “1.2” etc. “1.20” in a data room index means the 20th item under 1, but if your cell is formatted as General, Excel will treat it as a number and drop the insignificant zero.
Also be aware that Excel gives you a maximum of 8 grouping levels, so this solution won’t cover grouping deeper levels than that.
Now for the code. It’s fairly compact and quick to run. It determines the level of the current record by the number of tokens in the index string, then applies logic based on the current record’s relation to what has come before it in the loop, grouping at each level in the tree.
Modern Legal Support provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. Always back up your documents before running any code.
Option Explicit
' Code by Kenneth A. Hester
' www.modernlegalsupport.com
Sub GroupFoldersByLevel()
Dim i As Long
Dim r As Range
Dim rStart As Range
Dim rEnd As Range
Dim foundStart As Boolean
Dim rngGroup As Range
Dim level As Long
Dim maxLevel As Long
Dim levelAtRow As Long
Dim indexColumn As Long
Dim fileFolderColumn As Long
Dim startRow As Long
Dim atEndRow As Boolean
' ----------------------
' set these values based on your worksheet:
maxLevel = 7
indexColumn = 1
fileFolderColumn = 3
startRow = 3
' ----------------------
For level = 1 To maxLevel
DoEvents
Debug.Print "Processing level " & CStr(level) & "..."
foundStart = False
For i = startRow To ActiveSheet.UsedRange.Rows.Count + 1
Set r = ActiveSheet.Rows(i)
atEndRow = (i = ActiveSheet.UsedRange.Rows.Count + 1) ' close off last groups at end row
' determine level of current row based on number of tokens in index:
levelAtRow = UBound(Split(CStr(r.Cells(indexColumn).Value), ".")) + 1
If levelAtRow <= level Or atEndRow Then
' found a file or folder at or above the level
If Not foundStart Then
' then we found the start of a group
If levelAtRow = level And Trim$(LCase$(r.Cells(fileFolderColumn).Value)) = "folder" Then
' it's a folder at the level
Set rStart = r.Offset(1) ' start grouping the files below the folder
foundStart = True
End If
Else ' already found start of the group, so now we found the end
Set rEnd = r.Offset(-1)
If Not rStart.Row > rEnd.Row Then ' this takes care of empty folders
Set rngGroup = Range(rStart, rEnd) ' close the group
rngGroup.Rows.Group
End If
If levelAtRow = level And Trim$(LCase$(r.Cells(fileFolderColumn).Value)) = "folder" Then
' if the end is the start of another folder at the level
' reset the start row
Set rStart = r.Offset(1)
foundStart = True
Else
foundStart = False
End If
'Exit Sub
End If
End If
Next i
Next level
Debug.Print "done."
End Sub
After running the code:
You can download my Excel test file with the code here.
Have fun. Let me know if it works for you.
Kenneth Hester is a Microsoft Office Specialist Master (2013, 2010, 2007, 2003) and a Microsoft Certified Application Developer.