Create an index showing page numbers of any terrms 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.

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 v2

.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.