APRIL 2, 2022: Checking for overlapping deposition designations
If you have a list of page and line deposition designations and want to see not only where they match marked page and lines from a previous set, but also where they overlap, you can use the Excel spreadsheet that I have posted below.
- Begin by placing the page and line ranges for your new set of designations in columns A to D.
- Put the page and line ranges of the designations from a different set that you want to compare the new set against in columns M to P.
- In column E, enter a LOOKUP formula to find where the page listed in column A falls between the page ranges in columns M and O.
It is set to lookup where the value in A falls between the values in column M and O, and then return the value in column M.
4. The formula is repeated in column F, but set to return the line number in column N where the page in A falls between M and O.
5. The LOOKUP formula is then modified in column G to search for where the ending page range in the new set falls with the page ranges of the old set. We can fhus find both where a range in the new set begins within a range from the old set, falls entirely within it, or ends before the range from the old set does.
6. Repeat the formula from column G, in column H so that it returns the ending line number from the corresponding range in the old set: =LOOKUP(2,1/((C2>=M:M)*(A2<=O:O)),P:P)
7. In column I a IF . . . THEN formula is used to check if the page in A is greater than the returned range from the old set, and so then must be within the old range, or if the line number at the beginning of the new range is greater or equal than that in the corresponding range from the old set.
8. The formula in column J checks where the line number from the new range is less than or equal to that of the corresponding old range, if the page number is not less than the ending page number in the range from the old set.
Where a range from the new set falls entirely within a range from the old set, ‘WITHIN RANGE’ will appear in both column I and J. Where a range from the new set begins within a range from the old set, but continues after it, ‘WITHIN RANGE’ will only appear in column I.
Where the range from the new set does not begin within a range from the old set, but ends within it, ‘WITHIN RANGE’ will only appear in column J.
Note that this spreadsheet will not show instances where a range from the new set begins before and ends after a range from the old set. To find these instances, swap the old and new ranges.
APRIL 9, 2022: More on RelativityOne’s Staging Explorer
The Tip of the Night for September 23, 2021 discussed how to use Relativity Staging Explorer to upload and download data for RelativityOne, and tonight’s tip provides some additional detail.
Staging Explorer is a great asset. It will transfer metadata without altering it, and can help you transfer more then 60 GB of data or more than 100,000 files in as little as an hour. It’s possible to access Staging Explorer through the RDC . . . .
Staging Explorer has some limitations which you may run up against.
- Only one instances of Staging Explorer can run on a Windows operating system.
- You will need to have your firewall set to allow for outbound connections on particular ports. A connectivity check must always be performed before initiating a transfer in Staging Explorer. Access it through the drop down menu at the top right.
- Files with paths longer than 470 characters cannot be accessed.
- Staging Explorer will not let you mount an .iso image file from a disc.
- No more than 10 million files can be set to transfer at once.
APRIL 15, 2022: Augmenting Excel Row Heights by a Set Value
Recently when editing a long spreadsheet in order to confirm that each row was wide enough to display all of the text inside a cell, I ran into some trouble beyond that posed by cells with too much text to fully display with the maximum row height of 409.5. While Excel’s AutoFit Row Height tool on the Home tab usually sets most rows to the correct width, it will not infrequently cut off a line or two of text from rows with cells that contain more than a couple of thousand
In order to address this problem, you can make use of the macro posted here, by Robin. The vba code is copied below.
This code, will augment each row that is specified in a range by a fixed amount that you designate. So if you want to increase rows 2 to 5 by 10 points enter ‘rowRow.RowHeight + 10’ at the end of the fourth line, and specify 2:5 in quotes in the In Rows parentheses on the third line.
Option Explicit
‘v0.1.0
Sub sbChangeRowHeightMulti()
Application.ScreenUpdating = False
Dim rowRow As Range
For Each rowRow In Rows(“2:5”)
rowRow.RowHeight = rowRow.RowHeight + 10
Next rowRow
Application.ScreenUpdating = True
End Sub
