{"id":65893,"date":"2006-12-07T13:45:00","date_gmt":"2006-12-07T13:45:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/12\/07\/how-can-i-compare-a-list-of-names-in-one-excel-column-to-a-list-of-names-in-another-column\/"},"modified":"2006-12-07T13:45:00","modified_gmt":"2006-12-07T13:45:00","slug":"how-can-i-compare-a-list-of-names-in-one-excel-column-to-a-list-of-names-in-another-column","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-compare-a-list-of-names-in-one-excel-column-to-a-list-of-names-in-another-column\/","title":{"rendered":"How Can I Compare a List of Names in One Excel Column to a List of Names in Another Column?"},"content":{"rendered":"

\"Hey,<\/p>\n

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?<\/p>\n

— GS<\/p>\n

\"Spacer\"\"Hey,\"Script<\/a><\/p>\n

Hey, GS. You know, in general we Scripting Guys are charged with presenting people with facts; in turn, we\u2019re 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.<\/p>\n

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<\/i>! Whoa, take it easy; we aren\u2019t 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.<\/p>\n

Of course, we humans have a ready-made excuse for that behavior. \u201cWe just watch whatever happens to be on,\u201d we say. \u201cIt\u2019s not our fault if there aren\u2019t any TV shows made for dogs.\u201d Now, admittedly, that used<\/i> to be true; at one time there weren\u2019t<\/i> any TV shows for dogs. But now there are.<\/p>\n

Or at least there\u2019s one: The Dog Tales – The Mailman DVD<\/b><\/a>. To quote from the Web site, \u201cYour 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!\u201d So there you go, GS. If you \u2013 like so many of us \u2013 are concerned that your dog hasn\u2019t been watching enough TV lately, well, your prayers have been answered.<\/p>\n\n<\/thead>\n\n\n
\n

Interesting Tidbit<\/b>. There\u2019s a sample clip from The Mailman<\/i> on The Dog Tales Web site. What we found intriguing was the fact that, in several scenes, something happens \u2013 say, a dog is barking \u2013 and a caption appears at the bottom of the screen letting you know what the dog is saying. We assume that caption is meant as an aid to humans, but we don\u2019t know that for sure. After all, the DVD is<\/i> aimed specifically at dogs; it\u2019s even filmed in \u201cDog-E-Vision.\u201d (No, really, that\u2019s what it\u2019s called: Dog-E-Vision.)<\/p>\n

Of course, maybe dogs have been able to read along. Seeing as how they can\u2019t talk, though, we\u2019d have no way of knowing that.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

To be honest, we Scripting Guys don\u2019t 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\u2019d appreciate it if everyone could pass today\u2019s column on to Fido as soon as you\u2019re done with it:<\/p>\n

Set objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\nSet objWorksheet = objWorkbook.Worksheets(1)\nSet objRange = objWorksheet.Range(\"B1\").EntireColumn\ni = 1\nDo Until objExcel.Cells(i, 1).Value = \"\"\n    strName = objExcel.Cells(i, 1).Value\n    Set objSearch = objRange.Find(strName)\n    If objSearch Is Nothing Then\n        Wscript.Echo strName & \" was not found.\"\n    Else\n        Wscript.Echo strName & \" was found.\"\n    End If\n    i = i + 1\nLoop\n<\/pre>\n
Before we begin we\u2019re assuming that you \u2013 or your dog \u2013 have a spreadsheet that looks something like this:<\/p>\n
\"Microsoft<\/p>\n
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\u2019s find out.<\/p>\n
Um, just as soon as \u201cwe\u201d finish barking at that squirrel.<\/p>\n
OK. As you can see, we start off in pretty straightforward fashion, creating an instance of the Excel.Application<\/b> object and setting the Visible<\/b> 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:<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/pre>\n
With Excel up and running we then use this line of code to create a Range<\/b> object that encompasses all the cells in column B:<\/p>\n
Set objRange = objWorksheet.Range(\"B1\").EntireColumn\n<\/pre>\n
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\u2019re only interested in names that appear in column B, we limit our search range to, well, column B.<\/p>\n
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:<\/p>\n
Do Until objExcel.Cells(i, 1).Value = \"\"\n<\/pre>\n
Here we\u2019re 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\u2019ll then continue looping until we encounter a blank cell in column 1.<\/p>\n
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:<\/p>\n
Set objSearch = objRange.Find(strName)\n<\/pre>\n
As you can see, we simply call the Find<\/b> method, passing that method the name taken from column 1. If the name is found we\u2019ll get back an object reference to the cell where the name was encountered; if the name can\u2019t be found then, needless to say, we won\u2019t<\/i> 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:<\/p>\n
If objSearch Is Nothing Then\n    Wscript.Echo strName & \" was not found.\"\nElse\n    Wscript.Echo strName & \" was found.\"\nEnd If\n<\/pre>\n
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.<\/p>\n
That takes care of the first cell in column 1. We increment the value of i by 1 (so that we\u2019ll be working with the second row of data) and then loop around and repeat the process all over again. When we\u2019re all done we should get back something that looks like this:<\/p>\n
Jesper Aaberg was not found.\nJonathan Haas was found.\nKen Myer was found.\nLene Aalling was found.\nPilar Ackerman was found.\nSyed Abbas was not found.\n<\/pre>\n
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: <\/p>\n
\u201cComfort your dog. Praise your dog. Even remind your dog to stay away from your favorite shoes.\u201d<\/p>\n
All kidding aside, this DVD is probably a pretty good thing for dogs; no doubt it beats doing \u2026 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.<\/p>\n\n<\/thead>\n\n\n
\n
Interesting Tidbit<\/b>. This holiday seasons Americans will spend an estimated $5 billion on Christmas presents for their pets. And those cheapskate pets probably won\u2019t spend even half that much on us humans!<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"
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\u2019re really supposed keep our opinions to ourselves. However, […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[711,48,49,3,5],"class_list":["post-65893","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-application","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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\u2019re really supposed keep our opinions to ourselves. However, […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65893","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=65893"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65893\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=65893"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=65893"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=65893"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}