Sort Email Metadata Without Losing the Attachments

This routine is a big favorite in my New York Excel CLE course Tools of the Trade: Excel Skills Every Litigator Should Know. Click here for more information about the course.

Scenario: You have a set of email metadata corresponding to a privilege log, exhibit list, or other set of documents, and you want to sort it in chronological order. When you try to sort the entries, you discover that all the attachments get pushed to the bottom of the list, because the date and time fields for the attachments are empty! How do you get the attachments to “stick” to their parent emails?

Strategy: We’ll combine some little-known Excel tricks to “propagate” the Date Sent and Time Sent for every email to each of its attachments.  Then they will sort properly. First, create a new column next to the DateSent column and call it SortDate.

Copy and paste the DateSent values into the new SortDate Column

This is where the magic happens:

Select the entire SortDate Column by clicking on the letter C, and then hit the F5 button to launch the Go To dialog box.  (You can also launch this Dialog Box by selecting Find & Select from the Home Tab.) From there, choose ‘Special’ in the lower left-hand corner.

Select the option “Blanks” and hit OK.

Notice that all of the blank cells (except the first one) in Column C are now highlighted in blue, indicating that they are ‘selected.’ The first blank cell, C3, has a box around it, indicating that C3 is the “Active Cell.”

With all the blank cells selected, we can fill each blank cell with a formula that references the cell above it, which will contain the date of the most recent email in the list. Without clicking anywhere, type an equals sign (“=”) and then click C2, the date above that Active Cell. (Don’t hit enter yet!) It should look like this:

Now hold down the Ctrl key and hit Enter. This fills in every selected cell with what you have just typed–namely, a formula that points to the cell directly above it.

Every cell that was blank before now has the value of the most recent populated cell above it. This even filled in the dates where there are multiple attachments:

With those cells still selected, click the Right Justify button and the Italics button, so that our new values will be easy to distinguish from the existing values.

Now you can hit Enter to complete this step. Finally, let’s replace all of those formulas with the dates they generate to prevent mischief later on (and reduce the file size). Copy the entire Column and then use Paste Special – Values.

The dates for the attachments are now hard-wired into the spreadsheet. (Tip: After a copy & paste operation you’ll see “dancing ants” around the block of cells you pasted. You can get rid of that by hitting Escape.) Now repeat the same process for the TimeSent column and arrive at this:

Now you can sort the whole table by SortDate, then SortTime. Click Ctrl+Home to select cell A1, then navigate to the Sort button on the Data tab.

Click the Add Level button once so that you’ll have two criteria, and sort by SortDate, then SortTime, accept the default Order settings, and the entries will be sorted by oldest to newest, and then in the original order when multiple entries (such as attachments) have the same date and time.

Your entries are now sorted in chronological order, attachments and all.

Voila!

One final tip: before a big sort like this, make sure you know how to get back to the original order if necessary. If there isn’t already an obvious order to the rows (e.g., by Document ID number or privilege log entry) create a new Column A and number the rows all the way down. You can enter the first few and then double click the cross-hairs in the lower-right corner of the last cell you completed, and Excel will autofill the rest.

There are many other uses for this elegant routine.  For example, you could identify all of the emails and attachments from a given sender, by “propagating” the From field.

Thanks for reading. Feel free to comment, share, and re-post.