May 7, 2022 – Excel array formula to check when all columns match, but ignore blanks
Tonight’s tip of the night explains how you can use an array formula in Excel to confirm that values on a single row for multiple columns are the same, but discount any instances where a column has a blank entry. So if the same value to filled in one or more of the columns in the given range, and none of the other columns has a different value, or simply has a blank value, the result of the array formula will be TRUE.
In this example we have both numbers and text entered in columns B to E. The formula:
=(SUM(IFERROR(1/COUNTIF(B2:E2,B2:E2),0))=1)
. . . will check columns B to E on row 2. This is an array formula, so be sure to press CTRL + SHIFT when entering it to enclose it in curly brackets.
As you can see, regardless of whether numbers or text are use in a row range, the formula indicates if they match or differ, and skips over the cells with no content.
May 14, 2022 – Calculating the total page and line count for deposition designations
You can use the Excel file linked to at the bottom of this post to automatically calculate how many pages and lines are in a single deposition designation, and multiple designations.
The beginning and ending pages and lines for each range of testimony are entered in columns B through E. Column H has a formula which computes the total number of lines in a single range of testimony:
=IF(B2=D2,(E2-C2)+1,(E2-C2)+((D2-B2)*25)+1)
If the designation is only on a single page, it simply subtracts the line number in column C from column E. If this is not the case, it multiplies the number of pages by 25, and adds them to the difference between E and C to get the line total.
Column I contains the formula that divides the line count by 25, and rounds down the result to the nearest whole number, unless there are less than 25 lines:
=IF(H2<25,0,ROUNDDOWN((H2/25),0))
The formula in column J: =IF(I2=0,H2,H2-(I2*25))
. . .subtracts the line count for the rounded down result given in column I from the total line count, unless there is less than 1 page, in which case it returns the total line count from column H.
On the TOTAL row, the formula in cell I9 not only adds up the page total listed above in column I, but also gives the sum of lines in column J divided by 25, rounded down to the nearest whole number. This gives the total page count for all the designations given in columns B to E on the above rows.
=SUM(I2:I8)+(ROUNDDOWN((SUM(J2:J8))/25,0))
Finally, the formula in J9 subtracts [the total line count divided by 25 and rounded down to the nearest whole number, but then multiplied by 25 – to get a number divisible by 25] FROM the total line count, in order to get the number of lines left over when the lines adding up to full pages are counted .
=SUM(J2:J8)-(ROUNDDOWN((SUM(J2:J8))/25,0)*25)
May 21, 2022 – Create an index showing page numbers of any terms in Word
Don’t miss how easy it can be to create a word index in Microsoft Word. Using a concordance file you can tag any letter or number strings in a Word document and automatically prepare an index which shows which page numbers on the Word document the strings appear on.
Begin by creating a simple Concordance file, which is just a two column table with the string you want to tag in the left column, and the XE code tag you want to add to the string in the right column. In this case we will tag the strings with the strings themselves because that is the output we want in the index.
In this example, we want to find the pages on which the four words in this table appear . . .
. . . in a different Word document. Note that the concordance file will not be case sensitive. We save the concordance file as a separate Word document. In the document we are preparing an index for, on the References tab in the Index section, click Insert Index.
. . . click on the AutoMark button on the lower right and browse to the concordance file you just created. This will add XE codes to the document which will be the basis of the index.
Select the location where you want your index to appear, and then go back to Insert Index on the References tab and select Insert Index again and then simply click OK.
An index will be generated listing the strings followed by each page number on which they appear.
May 28, 2022 – special characters cause batch files to fail
I’m working on a trial right now so my posts will have to be short .
Note that if you use a batch file like the one discussed in the Tip of the Night for July 24, 2020, and the names for the renamed files include any of the special characters:
* . ” / \ [ ] : ; | , $ + # % ! @
. . . which cannot be used in the Windows file system, the batch file will fail, and files with those characters will not be copied. To get around this problem, use the PowerShell script described in the Tip of the Night for May 5, 2018.
*The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer. All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information. This policy is subject to change at any time. The owner is not an attorney, and nothing posted on this site should be construed as legal advice. Litigation Support Tip of the Night does not provide confirmation that any e-discovery technique or conduct is compliant with legal, regulatory, contractual or ethical requirements.