Category Archives: Microsoft Office

Excel – Multi-column VLookup

When analyzing data in Excel at times it is necessary to pull data from another data source based on more than one column. In this case you can use the MATCH function in an array formula. Just so you understand the general idea of an array, it is a collection of values. The match function is generally used to find a value in an array. Knowing this, we will use this Excel file to show how to engineer a multi-column VLOOKUP in a few simple steps.

Source Data Tab

Above is a screenshot of the data in the Source Data tab. On our Lookups tab I started off with just the first names and last names of some users. In order to pull the corresponding usernames and DOBs (dates of birth) I did the following:

  1. Setup a column indicating where the matching data was found by using the following array formula:
    {=MATCH(1,('Source Data'!$A$2:$A$27=$A1)*('Source Data'!$B$2:$B$27=$B1),0)}

    • It is important to note that even though the formula is surrounded by curly braces, those are put there by Excel (NOT MANUALLY) when executing the formula by using the CTRL+SHIFT+ENTER key combination.
    • The first parameter (1) indicates the value being searched for in the second parameter.
    • The second parameter is an array which remains such only because of the CTRL+SHIFT+ENTER key combination used to finalize the formula.

      • That will produce an array of 0s and 1s which are coerced into the numbers because of the multiplication operation.
      • If there was only one column checked and the multiplication were taken out, the array will be full of TRUEs and FALSEs. For example the formula=TRUE*TRUE results in 1 while TRUE*FALSE results in 0.
    • The third parameter 0 indicates that the first parameter must match be an exact match of one of the items in the array (second parameter).
    • The value returned will be the position (start at 1) of the value in the array if found. If not found an N/A error will be returned.
  2. Setup a new column which references the matching row column to pull the corresponding username if a matching row was found. If the row wasn’t found show the cell as empty:
    =IF(ISNA($C2),"",INDEX('Source Data'!$C$2:$C$27,$C2))
  3. Setup a new column which references the matching row column to pull the corresponding DOB if a matching row was found. Format the date using the YYYY-MM-DD format. If the row wasn’t found show the cell as empty:
    =IF(ISNA($C2),"",TEXT(INDEX('Source Data'!$D$2:$D$27,$C2),"YYYY-MM-DD"))

After doing the above three steps in row 2 of the Lookups sheet and copying the formulas down, the final result was this:

Lookups Tab

If I were to clean this up a bit more I might hide column C of the Lookups tab, but other than that there really isn’t much else you need to do. I know this may still seem a bit mysterious, but with practice you will begin to understand array formulas better and perhaps write other useful array formulas to get the job done. For all of you who came to figure out how to do a multi-criteria lookup, I hoped it helped but if not, comment on what needs clarifying and I will do my best to help. 8-)

JavaScript Snippet – Convert Number To Column Name

Following yesterday’s post, I thought it appropriate to write a quick JavaScript function which converts an integer into the corresponding column name:

/**
 * Takes a positive integer and returns the corresponding column name.
 * @param {number} num  The positive integer to convert to a column name.
 * @return {string}  The column name.
 */
function toColumnName(num) {
  for (var ret = '', a = 1, b = 26; (num -= a) >= 0; a = b, b *= 26) {
    ret = String.fromCharCode(parseInt((num % b) / a) + 65) + ret;
  }
  return ret;
}

You can click here to enter a positive integer and see the corresponding column name. If you need this function for a project, feel free to use it as long as you give credit. 8)

JavaScript Snippet – Parse A Spreadsheet Address

Today I am starting work on a quick flashcard app that will use Google Docs to read data from a spreadsheet. I thought I had written and published a function which parses an Excel like cell address and converts it into the column number and the row number but I wasn’t able to find it. Thusly I wrote the following parseAddress function:

/**
 * Takes in a cell address and returns an array containing the corresponding
 * column number followed by the row number.
 * @param {string} strCellAddr  The address of the cell (eg. AD32).
 * @return {!Array.<number>}  An array of two numbers:  the column number and
 *     the row number.
 */
function parseAddress(strCellAddr) {
  var i = strCellAddr.search(/\d/);
  var colNum = 0;
  strCellAddr = +strCellAddr.replace(/\D/g, function(letter) {
    colNum += (parseInt(letter, 36) - 9) * Math.pow(26, --i);
    return '';
  });
  return [colNum, strCellAddr];
}

This function takes the cell address as a string (eg. AD32) and converts it to an array (eg. [30, 32]). The first value in the array will be the column number and the second will be the row number.

Excel – Converting From UNIX Time

Today I was working on a one-time HIVE report which due to the amount of data that is in each table, took a significant amount of time to run. After running though, I had the data that I wanted except for the fact that I neglected to convert the UNIX timestamp into a text version of the date. Instead of re-running the query again, I decided to use a formula in order to convert the UNIX time into a meaningful string.

  1. First I used =DATEVALUE("01/01/1970") in order to get the number representation of the first day in UNIX time (January 1, 1970).
  2. Next I determined that UNIX time must be divided by the amount of seconds in a day (86400) in order to be in accordance with the way Excel converts dates to numbers.
  3. Finally I ended up with the following formula in order to convert the UNIX time stored in cell A2 into a text representation: =TEXT(A2/86400+25569,"YYYY-MM-DD HH:MM:SS")

It is important that there is no built-in way to determine the timezone offset of the current users computer with a function. On the other hand, if you already know the timezone offset, you could include this in your calculations. For instance, if the data was captured in a timezone of -0400, you could use the following formula: =TEXT([UNIX_TIME_CELL]/86400+25569-4/24,"YYYY-MM-DD HH:MM:SS").

From Office 2003 To Office 2007 or Office 2010

Did you know about the interactive guides provided by Microsoft to help Office 2003 users to migrate to the corresponding 2007 or 2010 software? If you are trying to get use to Office 2010, you can use the interactive guides on this page. If you are trying to get use to Office 2007, you can use the interactive guides listed below (or go here to download them):

I realize that this is a very late post, but I figured it is still worth mentioning for all of those who remember how to do something in the old version of Excel, Word, etc. but not in the newer version.  Click here for all of the information from an official Microsoft site.

VBA Snippet – GetTimezoneOffset()

One of the things I think is amazing about VBA is the fact that it is so incomplete in some ways. For instance, did you know that there is no native way to get the local timezone offset. Yesterday I was working on a VBA SOAP plugin for Excel and realized this horrible fact. Of course, my first instinct was to Google the answer. Unfortunately, not even Google could provide a satisfactory (meaning short) solution. Therefore, I came up with the following VBA function:

Public Function GetTimezoneOffset() As Integer
  Dim ie As New InternetExplorer
  ie.Visible = False
  ie.Navigate "about:blank"
  While ie.readyState <> READYSTATE_COMPLETE And Not ie.Busy
  Wend
  ie.Document.parentWindow.execScript "n = (new Date).getTimezoneOffset()"
  GetTimezoneOffset = ie.Document.parentWindow.n
  ie.Quit
End Function

Of course, there are downsides to this solution. The first (being minor) is the fact that you have to include a reference by going to Tools > References… and then selecting “Microsoft Internet Controls”. The second is the fact that every time this function is called, an instance of Internet Explorer is created and then quickly (speed depending on the computer) destroyed. Other than that, the function will work. Its purpose is to return the amount of minutes behind GMT (or UTC) time the computer is. This is the equivalent of doing the following in JavaScript:

var offset = (new Date).getTimezoneOffset();

Quick Excel Tip – Quickly AutoFit Columns & Rows

  1. Click on the top-left rectangle above the row numbers and to the left of the column names so that all of the cells in the worksheet will be selected.
  2. Move the mouse over the line between two column names (column headers) and double-click it. This will AutoFit all of the columns in the worksheet.
  3. Move the mouse over the line between two row numbers (row headers) and double-click it. This will AutoFit all of the rows in the worksheet.

Quick Excel Tip – Quickly Select Cells In A Range

One of the things that I still find funny is when I ask a user to select all of the values in a column (not the column itself) and then user starts from the top and drags the selection until they reach the end. Of course, for a couple hundred rows, this doesn’t take too long, but if you have thousands or tens of thousands, this can take a while. Therefore a quick tip to select a large range of non-empty cells in a column, you can do the following:

  1. Select the top-most cell in the desired range.
  2. Hold down CTRL (or if using a Mac COMMAND) and SHIFT on the keyboard and then press the down arrow key. This will select all of the cells in the range as long as there are no empty cells in the range.

If the range does contain empty cells, instead of dragging the selection from the top to the bottom, you can do the following:

  1. Select the top-most cell in the desired range.
  2. Scroll down to the end of range (which is usually the end of the sheet) by using the scroll bar.
  3. Press SHIFT and then select the last cell in the range.

If you know the address of the last cell, you can do the following:

  1. Select the top-most cell in the desired range.
  2. Type the address of the final cell of the range into the address box and press SHIFT + ENTER.

If you know the addresses of both the top-left and the bottom-right cells, you can enter those address delimited by a colon into the address box and then press enter. For example, entering A2:D8192 into the address box will select the cells from A2 to D8192.

There are probably other cool tricks as well, so if you have any to share with the world, you can post them in a comment. If your’s is good enough, I will definitely expand this article to include it and then give you credit. ;)

Excel – Coalesce Function?

Unfortunately, after googling for a while, I was unable to find a COALESCE function or something similar that natively exists within Excel. Therefore, I decided to write a quick-and-dirty formula that would approximate it. I used the following array formula to pull the first non-blank value from cells C2 to C10 in a worksheet:
=INDEX($C$2:$C$10,MATCH(FALSE,ISBLANK($C$2:$C$10),FALSE))

As you can see, I used three Excel functions get my desired result. It would be nice to have less, but you can’t expect everything from this poor man’s database. :D Anyway, the ISBLANK function does exactly what it sounds like it does: it indicates whether or not the specified cell is blank. Since this is an array formula, ISBLANK($C$2:$C10) evaluates to an array of 9 TRUE/FALSE values. The MATCH function searches for the first parameter in the specified array (the second parameter) and evaluates to the position of the first match. The reason I supplied FALSE as a third parameter was to make sure that if every value was blank, a #N/A error would be returned. The INDEX function takes an array as the first parameter, and the position in the array to evaluate to as the second parameter.

In order to make the the formula evaluate as an array formula, you must use the CTRL + ENTER key combination to instead of simply pressing ENTER after inputting the formula. If you are using a Mac, the key combination is COMMAND + ENTER.

In conclusion, this is not the optimal solution, but it works and it doesn’t require a UDF (AKA macro).

Excel – Day of the Week By Name

Have you ever had to work with a spreadsheet which contained a column full of dates, but you actually needed to see column full of the corresponding days of the week by name? Believe it or not, translating dates to the name of the day of the week is quite easy. Let’s say that we have the date 6/24/11 in cell A2. In order to find out what day of the week that day is, we can place this formula in another column on the same row:
=TEXT("DDDD", A2)

Now in this new cell, you will see the value Friday. As you have probably already guessed, if the entire column is filled with dates, you can drag this formula down through the entire column.