Use Excel's Solver Add-In To Get the Data for the Results That You Need

Excel includes an optional add-in (which can be accessed from File . . . Options . . . Add-ins) named 'Solver' which you can use to automatically alter the data listed in an array that the result in a formula is based on, so that this data matches the value you want the formula to result in.

When activated it will appear on the Data ribbon in a new group called, 'Analyze'.

We begin by selecting a cell which has the result of a formula in it, then opening Solver. In this example, the result of the sum of home runs on rows 2 to 15 in column I is given in cell I16, 223.

We indicate that want the result to be 300 in the box next to the 'Value Of' radio button.

We want to designate that the result should be given by only changing the totals for players who hit 15 or more home runs, and none of these players should hit more than 50 home runs.

After clicking 'Solve', the following update to the number of home runs is generated.

However we obviously can't use this result because it's not possible to hit 0.2 home runs in baseball.

We can fix the results by specifying in Solver that each entry in column I should be an integer by selecting the option for 'int' in the drop-down menu between the cell references and the constraint box.

Now when we click 'Solve' only whole numbers are listed in the array that gives us a sum of 300 home runs:

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.