Find and Correct Overlapping Deposition Designations

You can use the spreadsheet attached below to identify and correct overlapping deposition designation page and line ranges.

Begin by entering the beginning and ending page and line ranges in columns B to E, listing the deponent's name in column A.. It is necessary to have the ranges sorted by deponent name, beginning page number, beginning line number, ending page number, and then ending line number.

Next, pull down the formulas in columns F to I. The formula in G will find where an overlapping range begins, and the formula in H will find where it ends The formula in column will give a result of 'Review' for the ranges you need to correct, and blank for the ranges which can be left uncorrected in the final Excel file. The formula result in column F shows which ranges are simply duplicates.

Filter in column G for 'check', and then pull down the formulas in J to R.

Then filter in column H for check, and pull down the formulas in columns J to R.

The result will show the correct ranges on the same row as the beginning range in columns O to R.

The formula in column N will show where more than two ranges overlap with one another.

Overlapping Depo Designations v2

.xlsx

Download XLSX • 14KB

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)

Deposition Designations v3

.xlsx

Download XLSX • 12KB

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.