Using Excel's Filter Formula to Search Ranges for a String and Return Hits

Excel's FILTER function can be used to generate (by entering the formula once in a single cell) results in multiple cells, by searching for a value in one column, and returning the data from the complete range where there's a hit for that value.

So in this example, the FILTER function entered in cell G2, returns from the range A2:D8, those entries where 'Tom' is listed as the sales rep in column B.

If you need to search for where a word or phrase appears in multiple cells in a row, you can use SEARCH nested in an ISNUMBER formula, and return the full contents of any cell which contains the searched for string. [See the explanation for this formula in the Tip of the Night for September 20, 2021] So in this example, we want to see which cells in the range from columns B to D contain references to the painter named in column J.

The formula is composed by searching for the value in cell J1, on the second row between columns B and D, and then filtering down the results from that range.

=IFERROR(FILTER($B2:$D2,ISNUMBER(SEARCH(J$1,$B2:$D2))),"")

The IFERROR function has the effect of excluding the '#CALC!' which would result if the FILTER function did not find a result. Entered this way, the FILTER function will return multiple hits from the cited range in multiple cells to the right of the column in which it is entered. When the complete formula is copied to search for strings entered at cells K1 and L1 (we use absolute references for columns B and D by entering dollar signs, so the complete formula points to the correct array) however a #SPILL! error will result as the data is overwritten.

To avoid this problem, we nest the formula in a TEXTJOIN function like this, so each hit in the range from column B to column gets entered in a single cell.

=IFERROR(TEXTJOIN("; ",TRUE,FILTER($B2:$D2,ISNUMBER(SEARCH(J$1,$B2:$D2))),""),"")

We can collect the results returned for all 10 rows by then using a simple TEXTJOIN function, as described in the post for June 29, 2024.

Batch insertion of multiple links in PDF files with Bluebeam Revu

Bluebeam's Revu software provides numerous ways to edit PDF files which are not included in Adobe Acrobat. It was developed for use by professionals working in engineering and architecture, but it includes many functions which litigation support professionals will find useful.

Recently, I was asked to add hundreds of links to exhibit citations in a Rule 56.1 statement filed for a summary judgment motion for submission to the court. Adding the links in one by one would have taken up many hours. Revu made it possible for me to add in the links automatically after creating a .csv file with the textual references to the citations in one column, and the paths to the PDFs in the second column.

Revu's Batch menu has many functions. You can select Batch . . . Link . . . New

. . . and a dialog will open that will allow you to select one or more PDF files to process:

Note the .csv file should list absolute paths to the PDFs from their original loading location, but there is an option [click the button for 'Link Options' on the lower right] to add them as relative paths, so that when the PDF and the linked files are transferred to a new location, the links will still work.

You can choose to overwrite existing links in the PDF, and control whether or not the box around the designated citation has a border or is set to be invisible.

Revu gives you the option to jump to specific page numbers in a linked to PDF file.

As always, Litigation Support Tip of the Night has run tests, and can confirm that Revu will add in hundreds of links in a PDF more than 50 pages long in just a minute or two. I spot-checked the results and did not find any errors - the page specific links were added correctly.

There is a 14-day trial version of the software which includes the functions described in tonight's tip.

VBA code to resolve overlapping page ranges

When preparing excerpts of pages from a deposition transcript for cited ranges in a court filing, it may be quite common to end up with a list of beginning and ending page ranges in two columns. It would be nice to simply take those ranges, insert a hyphen in between the beginning and ending of each range, and a comma between each individual range to set an extraction range in Adobe Acrobat, so you end up with the PDF that you need.

However, it's quite likely that in this situation you'll end up with many overlapping ranges where one range appears in the middle of the range listed before it, or the range begins in the midway point of a preceding range, but then ends several pages after it.

It's possible to easily generate a list of page ranges that account for all pages in your list, but which don't overlap and which don't include duplicates. Be sure to list the ranges in columns A and B, and then sort the data in order by the beginning pages before running the code.

After running the macro, the ranges will be listed in columns D and E:

. . . the input for the extraction in Acrobat then goes like this:

Sub ConsolidatePageRanges()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim s As Long, e As Long
    Dim nextOutputRow As Long

    ' Set the worksheet to the active sheet
    Set ws = ActiveSheet

    ' Ensure data is sorted by Start Page for this logic to work correctly
    ' You might want to manually sort Column A first if needed

    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ' Check if there is data to process
    If lastRow < 2 Then
        MsgBox "No data to process (need at least one row besides header).", vbInformation
        Exit Sub
    End If

    ' Initialize the first range
    s = ws.Cells(2, 1).Value
    e = ws.Cells(2, 2).Value

    ' Set output location (e.g., Column D and E, starting from row 2)
    nextOutputRow = 2
    ws.Cells(1, 4).Value = "Consolidated Start"
    ws.Cells(1, 5).Value = "Consolidated End"

    ' Loop through the rest of the data
    For i = 3 To lastRow
        ' If the current start page is greater than the current consolidated end page,
        ' the ranges do not overlap, so write the consolidated range and start a new one.
        If ws.Cells(i, 1).Value > e Then
            ws.Cells(nextOutputRow, 4).Value = s
            ws.Cells(nextOutputRow, 5).Value = e
            nextOutputRow = nextOutputRow + 1
            s = ws.Cells(i, 1).Value
            e = ws.Cells(i, 2).Value
        Else
            ' If they overlap, extend the current consolidated end page if the new end page is greater.
            If ws.Cells(i, 2).Value > e Then
                e = ws.Cells(i, 2).Value
            End If
        End If
    Next i

    ' Write the last consolidated range
    ws.Cells(nextOutputRow, 4).Value = s
    ws.Cells(nextOutputRow, 5).Value = e

    MsgBox "Ranges consolidated successfully in columns D and E.", vbInformation

End Sub