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.
I apologize for posting such a basic tip tonight, but I have been working nonstop lately.
It's pretty well known that if you select a file in Windows Explorer with the SHIFT key held down, you will see the option in the right click menu to copy the full file path of the selected file. Don't miss that you can also select multiple files, and copy the paths for each to the clipboard all in one step:
The Excel spreadsheet posted below tonight can be used to generate a list of Bates number ranges, listing consecutively numbered ranges in a long list that has gaps, and includes ranges from different party's productions.
Enter your list of beginning and ending Bates numbers in columns A and B. Be sure they are sorted in numerical order. The formula in column C checks to see where the difference between the number (minus the Bates prefix) in column A is a different than one whole number, but it only checks the characters in each cell after the last non-numerical character appears.
=IF(RIGHT(A3,LEN(A3)-D3)-RIGHT(B2,LEN(B2)-F2)<>1,"Gap","Consecutive")
The formula is designed to do this because Bates numbers always end with numbers of several digits and almost always contain prefixes with letters, hyphens, underscores, spaces, and other non-numerical characters, and can sometimes contain numbers as well such as 'ACME-2004_00099454256', or '3M 0006532'.
The formulas in columns D and F search for the position where the last non-number appears, and the formula in column C references the result of those formulas.
=LOOKUP(2,1/(ISNUMBER(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"abcdefghijklmnopqrstuvwxyz@#$%^&+ _-:|.—"))),ROW(INDIRECT("1:"&LEN(A2))))
Thanks to tigeravatar for posting this LOOKUP formula here. Note that the formula will fail if the Bates prefix ends with any character other than these: abcdefghijklmnopqrstuvwxyz@#$%^&+ _-:|.—
It can used for more than one purpose where you need to find the last occurrence of a character or characters in a cell.
The formulas in columns E and G flag where a Bates number ends with a letter suffix. In these cases the Bates ranges will have to be manually checked.
The formula in column H checks for where the Bates prefix in column A differs from the preceding Bates prefix, and enters a new Bates number where the formula in column C indicates a gap appears.
=IF(LEFT(A7,D7)<>LEFT(A6,D7),A7,IF(C6="Gap",A7,H6))
The formula in the last column enters the ending Bates number for each consecutive range. Simply filter in this column to get the complete ranges for all Bates numbers in H and I, but be sure to manually correct the very last Bates number.
Litigation Support Tip of the NIght has been working longer hours this year than he ever has before, so please forgive him if some of the posts on this site are not as detailed as those from past years.
If you find that Excel spreadsheets for which you have added hyperlinks to a network drive using the HYPERLINK formula are inexplicably repointed to the users appdata folder on your C drive, you may be able to prevent this from reoccurring by turning off the AutoSave option. Go to File . . .Options and uncheck the option to autosave the file at ten minute intervals, or the option to autosave the .xlsx file on SharePoint. Also try moving the AutoRecover location to the same directory to the which the hyperlinks are pointed.
