Hey, Scripting Guy! In Excel, how can I compare a list of names found in one column with a list of names found in another column?

— GS

Hey, GS. You know, in general we Scripting Guys are charged with presenting people with facts; in turn, we’re really supposed keep our opinions to ourselves. However, sometimes there are things that just have to be said, whether anyone likes it or not. And this happens to be one of those occasions.

On his way to work this morning the Scripting Guy who writes this column learned something absolutely mind-boggling: in most households (at least in the U.S.) dogs are forced to watch the same television shows as everyone else! Whoa, take it easy; we aren’t accusing any of you of being this heartless. But, like it or not, when people turn on the TV they almost always turn on a program designed for, well, people. Your poor dog is just plain out of luck.

Of course, we humans have a ready-made excuse for that behavior. “We just watch whatever happens to be on,” we say. “It’s not our fault if there aren’t any TV shows made for dogs.” Now, admittedly, that used to be true; at one time there weren’t any TV shows for dogs. But now there are.

Or at least there’s one: The Dog Tales – The Mailman DVD. To quote from the Web site, “Your dog will never be bored again after meeting Collin, Whistler, McLendon and the gang. In this episode hair stands on end when the dreaded Mailman pays a visit to Long Leash Lane!” So there you go, GS. If you – like so many of us – are concerned that your dog hasn’t been watching enough TV lately, well, your prayers have been answered.

To be honest, we Scripting Guys don’t know if dogs are getting their fair share of scripts that can open an Excel spreadsheet and compare a list of names found in one column with a list of names found in another column, either. Because of that we’d appreciate it if everyone could pass today’s column on to Fido as soon as you’re done with it:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.Range("B1").EntireColumn
i = 1
Do Until objExcel.Cells(i, 1).Value = ""
    strName = objExcel.Cells(i, 1).Value
    Set objSearch = objRange.Find(strName)
    If objSearch Is Nothing Then
        Wscript.Echo strName & " was not found."
    Else
        Wscript.Echo strName & " was found."
    End If
    i = i + 1
Loop

Before we begin we’re assuming that you – or your dog – have a spreadsheet that looks something like this:

Our goal is to take a look at each name in column A and check to see if that name also appears in column B. How are we going to do that? Let’s find out.

Um, just as soon as “we” finish barking at that squirrel.

OK. As you can see, we start off in pretty straightforward fashion, creating an instance of the Excel.Application object and setting the Visible property to True; that gives us a running instance of Excel that we can see on screen. We then use these two lines of code to open the file C:\Scripts\Test.xls and bind to the first worksheet in that file:

Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)

With Excel up and running we then use this line of code to create a Range object that encompasses all the cells in column B:

Set objRange = objWorksheet.Range("B1").EntireColumn

Why do we do that? Because in Excel, you need to specify a range before you can do a search; your search then does its magic only within that set of cells. Because we’re only interested in names that appear in column B, we limit our search range to, well, column B.

After defining the search range we then assign the value 1 to a counter variable named i. That brings us to this line of code:

Do Until objExcel.Cells(i, 1).Value = ""

Here we’re setting up a loop that enables us to methodically check each and every value in column A. We begin the loop by looking at the value of cell 1, 1 (remember, we assigned the counter variable i the value 1). We’ll then continue looping until we encounter a blank cell in column 1.

Inside our loop we start off by grabbing the value of the first cell in column 1 and assigning it to a variable named strName. We then use this line of code to search the search range for the first instance of that name:

Set objSearch = objRange.Find(strName)

As you can see, we simply call the Find method, passing that method the name taken from column 1. If the name is found we’ll get back an object reference to the cell where the name was encountered; if the name can’t be found then, needless to say, we won’t get back an object reference. That means we can use this block of code to see whether or not we got back a valid object reference:

If objSearch Is Nothing Then
    Wscript.Echo strName & " was not found."
Else
    Wscript.Echo strName & " was found."
End If

Either way we then echo back the name taken from column 1, along with a message indicating whether or not that name also appears in column 2.

That takes care of the first cell in column 1. We increment the value of i by 1 (so that we’ll be working with the second row of data) and then loop around and repeat the process all over again. When we’re all done we should get back something that looks like this:

Jesper Aaberg was not found.
Jonathan Haas was found.
Ken Myer was found.
Lene Aalling was found.
Pilar Ackerman was found.
Syed Abbas was not found.

There you go, GS: we hope both you and your dog will find this script useful, and entertaining. You might find this of interest, too: for a few dollars more you can record a personalized message onto the DVD you buy for Spot. To quote from The Dog Tales Web site:

“Comfort your dog. Praise your dog. Even remind your dog to stay away from your favorite shoes.”

All kidding aside, this DVD is probably a pretty good thing for dogs; no doubt it beats doing … well, whatever it is dogs do all day when no one is home. Besides, if a personalized DVD could get Rover to make dinner or mow the lawn while he was out even the Scripting Guy who writes this column might buy one.