While a good percentage of my mail migrations are part of an Exchange Hybrid environment, I don’t always have that luxury. We still work with plenty of migrations to Office 365 from non-Hybrid environments, each with different migration toolsets.
When you get to a migration that is something like Google, Zimbra or even an Office 365 “tenant to tenant” migration, Microsoft Excel becomes part of your migration tools. These types of migrations are full of CSV exports from multiple sources and manipulation of these exports with various formulas. While some might argue that this data should be put into a database instead of spreadsheets, I generally don’t have access to a DBA and Excel offers more agility in manipulating the data.
Below are some of the Excel tricks that I use when managing this data.
History of the Spreadsheet
Stepping back a bit, the idea for this article came from a podcast that I was listening to on the origin of the electronic spreadsheet; at that time I happen to be sorting through massive amounts of data on spreadsheets from a sizable Office 365 tenant to tenant migration. The podcast was from NPR’s “Planet Money” and I’d recommend giving it a listen:
The Tricks
I’m sure that others have some tips and tricks that I haven’t thought of here or used before; if you have any others, drop a note in the comments and I’ll add them into the list. I’m by no means an Excel expert but the items below are some relatively simple tools that I often reach for.
Count
Pretty much the first thing I do with any spreadsheet that has a list of users, mailboxes, etc is get a count of the number of items. I want this to be dynamic as I filter through different aspects of the spreadsheet.
So in cell “A1” I enter the following (assuming that column B is fully populated):
=SUBTOTAL(3,B:B)-1
This gives me a nice dynamic count of my rows:
Filter
My second step is usually enabling filtering on the sheet. This is of course assuming we have the data we want to filter in a format to do so. Just highlight the columns you want to filter on and press “CTRL+SHIFT+L” or select the “Filter” button in the ribbon.
We can now filter on a values such as whether or not the user has an archive mailbox (and our “count” in A1 will update accordingly).
Dissecting Email Addresses
Sometimes you need to separate an email address into two parts: the user and the email domain. Perhaps you need to generate an address with a new email domain for everyone. There are a couple of ways to do this.
You can use the “Text to Columns” feature within Excel and split the column using the “@” as the delimiter. This will want to overwrite the neighboring column and modifies your source data which I often try to avoid.
Another option is to add a new column and use this formula to extract the user (cell C2 below):
=LEFT(B2,FIND("@",B2)-1)
or this formula to extract the SMTP domain (cell D2 below):
=RIGHT(B2,LEN(B2)-FIND("@",B2)))
VLOOKUP
The VLOOKUP function will quickly become your friend in Excel; knowing how to use it properly will be critical. The idea is that you can take a value and see if it’s in a column on another sheet; if it’s found, you can return a value from the row where the searched value was found. Since this one is a little more complex, you might also want to check the Microsoft syntax on this function.
I’ll often use this in situations where I’m comparing source and target mailbox lists or perhaps comparing a list of users against a list of migrations and their statuses.
In the example below, I have two sheets: “UsersToMigrate” and “MigrationStatus”. The “UsersToMigrate” would typically be a list I’ve exported out of the source environment and “MigrationStatus” would be similar to a report you might export out of a tool like “MigrationWiz”.
In cell C2 of “UsersToMigrate”, I’ve entered the following formula:
=VLOOKUP(B2,MigrationStatus!A:B,2,FALSE)
Basically I’m looking for B2 in column A of the “MigrationStatus” sheet and then returning the value in the second column (column B) of that row. Critical here is the use of the “FALSE” flag; if you don’t use this, the default is “TRUE” which looks for the closest result and not necessarily an exact match.
VLOOKUP as Boolean
Occasionally you’re using the above formula against a dataset that doesn’t necessarily contain all the values we’re searching on; values not found will come back as “#N/A”. Maybe we just want to know if the value in B2 exists in the “MigrationStatus” sheet and treat it as a Boolean.
We can do this with the formula:
=ISTEXT(VLOOKUP(B2,MigrationStatus!A:A,1,FALSE))
This will return “TRUE” or “FALSE” and then we can easily use the filters to find out how many have been found.
Summary
Certain types of mail migrations result in data being managed through a series of exported CSVs. Functions within Excel can be used to make sorting through the data manageable and allow you to compare values between multiple data sources.
Did you find this article helpful?
Leave a comment below or follow me on Twitter (@JoePalarchio) for additional posts and information on Office 365.
Looking to do some more reading on Office 365?
Catch up on my past articles here: Joe Palarchio.
