Tag Archives: Excel

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-)

Python – Convert Excel Column Name To Number

A few days I wrote a post about how to convert an cell address into coordinates (column number, and row number). I am working a quick Python script for work to import CSV data into a DB. In order to allow the user the ability to enter either the column number or the column name I used the following function to convert column names into number:

def colNameToNum(name):
    pow = 1
    colNum = 0
    for letter in name[::-1]:
            colNum += (int(letter, 36) -9) * pow
            pow *= 26
    return colNum

As you can see, all you have to do is pass it the name of the column (letters) and the correspond column number will be returned. Have fun! 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").

Batch Excel To CSV Converter Application

I have been truly fascinated by the amount of visitors that I get because of the simple XLS/XLSX to CSV converter script that I made a while back (original post). For that reason I have created an HTML application (HTA) which offers the same functionality and then some:
XLS To CSV Converter
DOWNLOAD THIS APP

How this Differs from the Script

  • There is an easy to use interface.
  • You can customize how the resulting files are named.
  • You can filter which sheets will be exported as CSVs using simple text matches and/or regular expressions.
  • You will see the results of the conversion process in one place after the process is finished.

Using It

  • Download it from here.
  • Unzip the .HTA file.
  • Double-click on the .HTA file.
  • Click the Browse for Folder button and browse for the folder that contains the XLS and/or XLSX files whose worksheets should be exported as CSVs.
  • Modify the CSV Naming Schema as you like (hints are given in the application).
  • If you would like to only export worksheets with specific names, enter those names in the Sheet Filters textbox (only one per line). You may also use JavaScript-style regular expressions (one on each line) to match the sheets that you want to convert.
  • If you would like to remove the linefeed characters from all cells, check the appropriate checkbox.
  • Click the Start Conversion button and wait for the results to appear.

Version History

  • 1.0 – The first version.
  • 1.0.1
    • Fixed the error thrown when specifying a normal string as a “Sheet Filter”.
    • Fixed the copyright symbol in the “About” message box.
  • 1.1.0
    • Added the ability to specify the delimiter.

Plans for Improvement

As of right now this application is very simple, but I do hope to beef it up a little as time goes on. Let me know if you have any comments, questions, suggestions, or issues with this new application. Most of all, have fun!!! 8-)

VBA – Encode URL Query Parts In Excel

Have you ever had a column text for which you wish you could easily create links? For example, let’s say that you have a column of search words (or phrases) that should link to the Google page for each search. How would you do it? First of all, you would probably want to be able to build the URL in a separate column. Unfortunately, Excel doesn’t provide a way of encode URL parameters. Therefore, you can use the following as your own UDF:

Public Function encodeURL(ByVal queryPart As String)
    Dim c As String
    While Len(queryPart) > 0
        c = Left(queryPart, 1)
        queryPart = Mid(queryPart, 2, Len(queryPart) - 1)
        If c Like "[A-Za-z0-9._~-]" Then
            encodeURL = encodeURL & c
        ElseIf c = " " Then
            encodeURL = encodeURL & "+"
        Else
            encodeURL = encodeURL & "%" & Right("0" & Hex(Asc(c)), 2)
        End If
    Wend
End Function

That function will take in one string and output the equivalent as an encoded URL query part. That function should be the equivalent of PHP’s urlencode() function. Tomorrow, we will see how this function can actually help you to be able to create links for an entire column.

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.

XLS Colors for JasperReports

One of the things that I often end up Googling is a list of the 56 default XLS colors. I finally decided to compile my own list so that I can simply copy and paste the value that I need to use for either the Forecolor or Backcolor in the properties pane:

Name Jasper Value Hex Code R G B
Black [0,0,0] #000000 0 0 0
White [255,255,255] #FFFFFF 255 255 255
Red [255,0,0] #FF0000 255 0 0
Green [0,255,0] #00FF00 0 255 0
Blue [0,0,255] #0000FF 0 0 255
Yellow [255,255,0] #FFFF00 255 255 0
Magenta [255,0,255] #FF00FF 255 0 255
Cyan [0,255,255] #00FFFF 0 255 255
[Color 9] [128,0,0] #800000 128 0 0
[Color 10] [0,128,0] #008000 0 128 0
[Color 11] [0,0,128] #000080 0 0 128
[Color 12] [128,128,0] #808000 128 128 0
[Color 13] [128,0,128] #800080 128 0 128
[Color 14] [0,128,128] #008080 0 128 128
[Color 15] [192,192,192] #C0C0C0 192 192 192
[Color 16] [128,128,128] #808080 128 128 128
[Color 17] [153,153,255] #9999FF 153 153 255
[Color 18] [153,51,102] #993366 153 51 102
[Color 19] [255,255,204] #FFFFCC 255 255 204
[Color 20] [204,255,255] #CCFFFF 204 255 255
[Color 21] [102,0,102] #660066 102 0 102
[Color 22] [255,128,128] #FF8080 255 128 128
[Color 23] [0,102,204] #0066CC 0 102 204
[Color 24] [204,204,255] #CCCCFF 204 204 255
[Color 25] [0,0,128] #000080 0 0 128
[Color 26] [255,0,255] #FF00FF 255 0 255
[Color 27] [255,255,0] #FFFF00 255 255 0
[Color 28] [0,255,255] #00FFFF 0 255 255
[Color 29] [128,0,128] #800080 128 0 128
[Color 30] [128,0,0] #800000 128 0 0
[Color 31] [0,128,128] #008080 0 128 128
[Color 32] [0,0,255] #0000FF 0 0 255
[Color 33] [0,204,255] #00CCFF 0 204 255
[Color 34] [204,255,255] #CCFFFF 204 255 255
[Color 35] [204,255,204] #CCFFCC 204 255 204
[Color 36] [255,255,153] #FFFF99 255 255 153
[Color 37] [153,204,255] #99CCFF 153 204 255
[Color 38] [255,153,204] #FF99CC 255 153 204
[Color 39] [204,153,255] #CC99FF 204 153 255
[Color 40] [255,204,153] #FFCC99 255 204 153
[Color 41] [51,102,255] #3366FF 51 102 255
[Color 42] [51,204,204] #33CCCC 51 204 204
[Color 43] [153,204,0] #99CC00 153 204 0
[Color 44] [255,204,0] #FFCC00 255 204 0
[Color 45] [255,153,0] #FF9900 255 153 0
[Color 46] [255,102,0] #FF6600 255 102 0
[Color 47] [102,102,153] #666699 102 102 153
[Color 48] [150,150,150] #969696 150 150 150
[Color 49] [0,51,102] #003366 0 51 102
[Color 50] [51,153,102] #339966 51 153 102
[Color 51] [0,51,0] #003300 0 51 0
[Color 52] [51,51,0] #333300 51 51 0
[Color 53] [153,51,0] #993300 153 51 0
[Color 54] [153,51,102] #993366 153 51 102
[Color 55] [51,51,153] #333399 51 51 153
[Color 56] [51,51,51] #333333 51 51 51

JasperReport Excel Options

Over the past year I have been learning about so many different OpenSource packages it isn’t even funny.  One thing that is available for free, but also has a paid version (which we at using at UniTek Global Services), is JasperServer.  Right now we are on version 4.2.1.  When we first rolled it out, everything seemed to work excellently with the exception of three things:

  1. In the report template, I placed the company logo to the left of two rows:  the report title and the description/generation date.  Whenever I generated the report as an XLS via Jaspersoft iReport Designer, the image would load correctly.  On the other hand, whenever I generated the report as an XLS via the JasperServer, the space for the image was left there without an image.
  2. Once the image issue was resolved (I will describe how soon), the XLS generated via the JasperServer would load the image on the same row as the title but it wouldn’t extend into the cell directly below so that it would appear to the left of the description as well.
  3. XLS reports generated via the JasperServer were not showing borders on any of the cells no matter how large I made the borders in the designer.

If you encounter these issues in your JasperReport template, do the following:

  1. In the Report Inspector pane, click on the top-most element (which is the template).
  2. In the Properties pan, select the Properties row and then select the elipsis at the right of the row.
  3. Show Images In XLS:
    To ensure that images will appear when exported as XLS, do the following:
    1. Click the Add button in the Properties window.
    2. Enter the following as the Property name:
      net.sf.jasperreports.export.xls.ignore.graphics
    3. Enter the following as the Property value:
      false
    4. Click OK to close the Add/modify property window.
  4. Allow Cells To Span Multiple Rows In XLS:
    To ensure that cells can span multiple rows when exported as XLS, do the following:
    1. Click the Add button in the Properties window.
    2. Enter the following as the Property name:
      net.sf.jasperreports.export.xls.collapse.row.span
    3. Enter the following as the Property value:
      false
    4. Click OK to close the Add/modify property window.
  5. Show Borders In XLS:
    To ensure that images will appear when exported as XLS, do the following:
    1. Click the Add button in the Properties window.
    2. Enter the following as the Property name:
      net.sf.jasperreports.export.xls.ignore.cell.border
    3. Enter the following as the Property value:
      false
    4. Click OK to close the Add/modify property window.