Excel VLookup Function

A wonderfully powerful and easy to use way of comparing data across Excel workbooks is the VLookup function. I've been helping a lot of people with this function lately, so I thought I'd get a quick tutorial out there.

Let's start with the syntax of the VLookup function:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Here's an example of how you would use VLookup to find rows on the "OtherSheet" worksheet that match something in the first row of your main worksheet:

=VLOOKUP(A1, OtherSheet!A$2:B$100, 2, FALSE)

Super. So what does this all mean?

lookup_value: The cell you want to match to the other sheet.
table_array: The range where your lookup_value exists in the first column.
col_index_num: The column of table_array that you want to show if you have a match. (First column is 1.)
range_lookup: Optional. TRUE will find matches “similar” to your lookup_value, and FALSE will only find an exact match.

As an example, you have a list of states with their populations on Sheet1. On Sheet2, you have a list of states with the number of lakes they have. Sheet1 lists all states but some are missing from Sheet2.

Option 1: Manually match the states together and copy the values over. 1 by 1.

Option 2: Use a VLookup function. In column C of Sheet1, you would use the vlookup function to see if the value in column A of that row (lookup_value) is found anywhere in column A of Sheet2 (table_array). Also included in table_array is the column that lists the number of lakes. (If there were 10 states listed, the range might be A1:B10.)

Warning!: When typing the range in your VLookup function, remember to put the dollar sign in front of the row numbers. Otherwise, as you copy the function down Sheet 1, the range will move down with you. So, when you get to row 10, rather than having the range of A1:B10, the range will be A10:B19. Use the dollar sign ($) and Excel knows you want the numbers to remain static.

Advanced VLookup Hack:
Whenever your VLookup doesn't find a match, it insists on putting #N/A in the cell. Needless to say, if you're making a report for someone, you don't want all of these ugly values throughout your sheet. You could sort your data and delete all of the #N/A values manually, but you'd also be deleting the VLookup formulas. Therefore, if your data changes you might miss values that you otherwise would have matched.

To get rid of the #N/A values automatically, use this formula:

=IF(ISERROR(VLOOKUP(…)), “Val if not found”, VLOOKUP(…))
For Example:
=IF(ISERROR(VLOOKUP(C13,'State-List'!C$2:E$100, 3, FALSE)),"",VLOOKUP(C13,'State-List'!C$2:E$100, 3, FALSE))

In the above example, we check to see if the VLOOKUP results in an error. (ie, value not found)
So if the “IF” does result in an error, we put the “Val if not found” value in the cell.
(Generally, you’d simply use back-to-back quotes to enter a null value into the cell.)
Otherwise, we run the VLOOKUP as originally intended.
(Replace the dots with the actual VLOOKUP parameters)

You don't even need to understand this formula to use it. Simply type it exactly as shown above, except, insert your own VLookup function in both places where it says VLOOKUP(...) and remember that whatever you type within those quotation marks will show up if there is no match for your VLookup. I generally recommend back to back quotes for a null value, or a zero if you're working with numbers.

Well I hope that helps clarify what Excel's VLookup function is all about. If you found this post helpful, please consider subscribing to my RSS feed. That way you won't have to remember to come back when I post another great Excel tip.

Excel Macro to Generate SQL INSERT Statements

I am in the process of creating a database of County information, and I needed to insert several thousand records. I was able to get the data into Excel, but now I needed to write an SQL Insert statement to get the records into my database. SQL wizards, I'm sure you know a more efficient way to write this INSERT. Actually, what is generated is a series of INSERT statements. If you'd like to share your knowledge, please leave a comment, and I'd gladly update the macro for your improved format.

How it works:
Let's start with what my spreadsheet looks like:
I'm using Row 1 as my header.
The values are: state, county, statefips, countyfips, and fipsclass
Beginning on row 2 I list that information.
Columns A, B and E are text.
Columns C and D are Integers. ***Note this in the macro when modifying.***

Upon running the macro, it will ask for the start row. Enter the number for the row that begins your data.

It then loops through columns A through E and puts those values into variables.

Then, it writes the insert statement, with our variables included, to the file specified by the "MyFile" variable. Currently, it will be written to the active/default Excel directory.

The macro will then loop down all of the rows until it finds a blank.

This macro is fairly short, so I'm copying it below.
I just realized that there are several things I could change to optimize this code and make it more useful. I'll try to get an update out soon. Until then, what would YOU change in this code?

Sub generateInsert()

Dim state As String
Dim county As String
Dim statefips As Integer
Dim countyfips As Integer
Dim fipsclass As String
Dim myRow As Integer
Dim myCol As Integer

myRow = InputBox("What row to START on?")
myCol = 1
MyFile = "insertStmt.txt"

'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum

Do Until ActiveSheet.Cells(myRow, myCol) = "" 'Loop until you find a blank.

state = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
county = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
statefips = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
countyfips = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
fipsclass = ActiveSheet.Cells(myRow, myCol)

myCol = 1 ' Return to column A
myRow = myRow + 1 ' Move down 1 row

Print #fnum, "insert into countyTable (state, county, statefips, countyfips, fipsclass)"
Print #fnum, "values ('" & state & "', '" & county & "'," & statefips & ", " & countyfips & ", '" & fipsclass & "');"

Loop

' Close the file
Close #fnum

End Sub

I'm looking forward to your comments.

- Jason

Excel Macro : Spreadsheet to HTML

So I have a customer that needed a registration page for their E-University. This consisted of about 500 courses in various categories.

I got the ~great idea to implement an AJAX Accordion to contain the categories. Within each accordion pane, I needed separate tables to house the various sub-categories. So how do I get a list of courses, with course codes and hyperlinks into a properly formatted accordion control?

I thought about doing this manually (for about 1 second), then I decided to try writing a macro. I don't have a ton of macro or VB experience, but this was the only thing I could think of.

Enter the reason for this post...

I searched all over the Internet for an easy way to export Excel to HTML. There were a few examples, but I found them really hard to follow, and most were too involved for my needs. (I thought)
Note: The material presented here was learned through trial and error. I am not a macro, VB, or VBA expert. I know there is a better way to go about this. Unfortunately, I couldn't easily find it online, so I hacked this together. If you are an expert, I would love for you to comment so we can have an optimized version of the code we should be using. In the end, this code worked great for me. You might not have the same results, so use at your own risk.

Please download the code for the macro here: http://www.businesshut.com/Excel_To_HTML_Macros.txt
Download the sample spreadsheet here: http://www.businesshut.com/E-University_Macros.xls
The output text file will be similar to this: http://www.businesshut.com/ExcelToHTML.txt
The resulting .aspx file will be made available soon.
I recommend following along with the example files.


Now, for a tour of the sub-routines...

First, you'll see "getHyperlinks"

In column C, the Course#'s are all hyperlinks. However, I needed the hyperlink separated from the Course# in my html.

getHyperlinks starts in cell C4. (you'll change this per your situation.)
We have a "Do Until" loop that will iterate for the number of rows you enter here. If you enter more rows than you need, the loop will simply write blank values to the cells in column E.
If a hyperlink is NOT found in the cell, we use a blank string.
If a hyperlink IS found, we use Selection.Hyperlinks(1).Address to pull out the URL.

We then use Selection.Offset(0,2).Select to move 0 cells down and 2 cells right, and we write the URL there.

Add 1 to i and repeat until we get to 400.

Great, now I have all of the URLs as text in their own column.


On to exporting this information as HTML...


Unfortunately, much of this code was pulled from random sources on the web, so I can't even explain how every line works. However, between this post, and the inline comments, you should be able to figure out how to modify it for your own uses.

Line 36: The "MyFile" variable dictates where the resulting text file will be saved.

We create "fnum" as a FreeFile(), and we'll use
Print #fnum, "string text"
to write each line to the file specified in the MyFile variable

Line 60: myRow was hard coded to 2 because that's where my data began. You may need to change this.

Now we start creating the accordion panes.
***Note: This macro only creates the accordion PANES. Create an accordion first, (another topic), and just use the code generated by the macro as your accordion panes. ***

Line 63: We get the "courseGroup" by pulling the value from the row specified by "myRow" and column 1 of the current sheet.
*One last time I'll mention that when I specify cells, that you may need to change them per the format of your spreadsheet.

Line 66: Now we start writing to the file.
This section creates the header information for the AccordionPane.
Everything in quotes is added literally, and the variables are inserted where necessary.

Line 74: Add 1 to myRow to move down to the Course Category and pull that value into our code.
This section labels this part within the AccordionPane as a new category and starts the table that will contain the course information.

Line 91: This is the main loop. (1000 is an arbitrary number. However, it is referenced later in the loop.)
Now that we have the AccordionPane and the category information, we'll start looping through the courses.
Back up on line 48, we set cl = 1
This was done to help me group various controls and reference them easily. I was having trouble using "FindControl" dynamically with a MasterPage and an AJAX Accordion control, so this was my solution. I might have been able to use a Checkbox Array, but I couldn't get the formatting I wanted. Each group of controls is simply named with the same number on the end. So if someone clicks "chk1", which is the checkbox to select the course, I know that "lblchk1" will contain the corresponding information for that course. Yes, crude but effective.


Line 125: (By "blank row" I mean that column B is blank.)
If we have 1 blank row, it indicates a new category.
If we have 2 in a row, it indicates a new Accordion Pane.
And if we have 3 blank rows, we're all done.

:: 1 blank row::
Line 129 skips down another row and checks to see if there's a second blank row, but we'll assume it's not.
Jump to the Else on line 182.
We step back a row to get the information that we skipped while checking the next row.
Line 184 ends the table for the previous category.
Line 187 gets the new category info.
Lines 188-199 set up the title and table for the new category.

Line 207 : Loop again from Line 91 until we hit 1000 iterations.

::If we find a second blank row in the IF statement on line 129::
Add another row to myRow and check to see if we have 3 blank rows in a row.
*We're assuming we only have 2.
Jump to the Else on line 144.
Now we go back 2 rows to get the heading information that we skipped.
Then we end the table for the previous section, and end the Content section of the AccordionPane. We also end the AccordionPane and start the header information of our new one.

Line 157 begins the new AccordionPane, and Line 167 begins the heading and table information for the first category within the new AccordionPane.

EndIf sends us to Line 207 : Loop again from Line 91 until we hit 1000 iterations.

:: 3 blank* rows:: (*Column B is blank.)
Line 135 : End the table for the current category.
Line 138 : End the Content section of the Accordion Pane.
Line 140 : End the Accordion Pane.
Line 143 : Set i to 1000 so we don't loop again.

Line 213 : Close the file for writing.
End Sub

~~~

Remember when I said I thought the other examples were "too involved" for my needs? I was wrong.
I realize this example is a bit involved, and it's very specific to my project. If you want to see a simplified example, just send me a comment, and I'd be happy to set one up.

I look forward to everyone's thoughts on how I went about this solution, and I'm really interested to see how you would have done it.

- Jason Green