Sign in to follow this  
Followers 0
404NotFound

Checking name matches between excel spreadsheets

5 posts in this topic

So I inherited a script from a previous programmer. It checked all the names in a spreadsheet against a database, but now that database is no longer active and I must see if any of the names show up in another spreadsheet instead.

Below I have the code to select the first spreadsheet (the one containing the names I want to check) and collect their names.

Would the code for the second spreadsheet (the one I want to search for the names) be similar?

I'm fairly new to programming and especially AutoIt, any help would be greatly appreciated.

Thanks!

$dbName = FileOpenDialog("Please Select Your Query Data Spreadsheet", @MyDocumentsDir & "\", "Microsoft Excel Files (*.xls;*.xlsx)", 1 )

If @error Then

MsgBox(4096,"","No File(s) chosen")

EndIf

; Open the file into an Excel Instance

$oExcel = _ExcelBookOpen($dbName)

; Make the desired sheet active, ::HARDCODED::

_ExcelSheetActivate($oExcel,"Monthly_Queries")

; Pull the sheet into the big array

$result = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 0)

; Close the Excel instance

_ExcelBookClose($oExcel)

; Count the number of people to query

$numEntries = UBound($result)

Share this post


Link to post
Share on other sites



The second sheet should be opened and read in a similar way, just changing the name of the array and variables for the second sheet.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

What function should I use to search for a match once I have the spreadsheet arrays? I'm hoping to be able to find any individual name matches from a large list of names.

Share this post


Link to post
Share on other sites

Also thank you for your help Brewman :)

Share this post


Link to post
Share on other sites

I'd use either _ArraySearch, or the version of _ArrayBinarySearch that's in the modified _Array.au3 file linked in my signature, this one will search a 2D array while the original will only work on a 1D array. You'd have to sort the array before searching it to use the binary search function, but it's probably over 100 times faster than the ArraySearch routine.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0