Excel MATCH function fails

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
Supergrover
Posts: 3
Joined: 01 Apr 2023, 23:03

Excel MATCH function fails

Post by Supergrover » 01 Apr 2023, 23:25

Hello,

Really appreciate the AHK forums...many topics have helped me over the last few years. This is my first post: hoping someone can assist.

I need to catalogue data in Excel and be able (in an AHK script) to search multiple columns very quickly to find matching rows that match on all of several columns.

Here's my problem: inside of Excel itself, I can use the Index-Match functions to get it done. Works perfectly. However, I cannot find any way to write this same command in AHK and make it come up with the same (correct answer).


For example: this Excel formula (when run in Excel itself) finds row 27 as the row with 'rehab' in column A and 'pain' in column B:

=MATCH("rehab"&"pain",A:A&B:B,0)

Here are my multiple attempts in AHK to write the same function: NONE give the correct answer:

Code: Select all

XL := ComObjActive("Excel.Application")
; msgbox % xl.match("rehab"&"pain",xl.range("A:A"&"B:B")) ; Way off
; msgbox % xl.match("rehab"&"pain",(xl.range("A:A")&xl.range("B:B"))) ; way off
; msgbox % xl.match("rehab"&"pain",xl.range("A:A")&xl.range("B:B")) ; way off
; msgbox % xl.match("rehab"&"pain",xl.range("A1:A100")&xl.range("B1:B100")) ; way off
; msgbox % xl.match("rehab"&"pain",xl.range("A:A&B:B"),0)  ; range error
; msgbox % xl.match("rehab"&"pain",xl.range("A:A"&"B:B"),0)  ; returns 23 when correct should be 27
; msgbox % xl.match("surg"&"urology",xl.range("A:A"&"B:B"),0)  ; returns 9 when correct is 11
It will work fine if I just want to search one column... but I'd actually like to search about 4. Before I throw in the towel and write my own search algorithm, wanted to see if anyone here knows a way to make the native match function work correctly in AHK.... Thanks very much!!


[Mod actions: Move topic from “About This Community.” Added [code][/code] tags. Please use them yourself when posting code.]
Attachments

[The extension xlsx has been deactivated and can no longer be displayed.]


User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Excel MATCH function fails

Post by flyingDman » 02 Apr 2023, 01:14

Not with Match or with worksheetfunction.match. I wonder if it is possible with VBA. It's easy enough to do with a simple for loop (even with > 2 columns) but no one-liner here...

Code: Select all

xl := ComObjActive("excel.application")
for c in xl.Range("A:A")
	if (c.value = "Rehab" && c.offset(0,1).value = "pain")
		msgbox % c.row
If you have many rows, use a safearray:

Code: Select all

sarr := xl.range("A:B").value
loop, % sarr.maxindex(1)
	if (sarr[a_index,1] = "Rehab" && sarr[a_index,2] = "pain")
		{
		msgbox % a_index
		break
		}
14.3 & 1.3.7

Supergrover
Posts: 3
Joined: 01 Apr 2023, 23:03

Re: Excel MATCH function fails

Post by Supergrover » 02 Apr 2023, 13:41

Ok. Thanks! I guess I was hoping that tapping into some inherent Excel function would be faster than looping ....if I need to loop through several thousand rows. Maybe not, though. Not entirely sure how robust excel 365 is but I will likely have multiple users using an excel com object for same excel doc at same time: so was thinking max speed is my best friend (?) for error reduction.

I'm also finding that just opening excel (not visible) seems to be slowish (separate to above data issue, I'm all dynamically populating dropdown boxes on GUI with data from Excel) ... so maybe I'm better off reading lines or the entire doc via a read loop of a text file (CSV or otherwise)? (Anyway: I think there are other threads on this so I can explore that a bit)..; thx for the input...

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Excel MATCH function fails

Post by flyingDman » 02 Apr 2023, 15:26

You can also use the MS Database Engine and a SQL statement to query the Excel file. (MS Database Engine is free and can be downloaded here). It is faster than opening excel in the background. It is faster than the safearray method I showed above.
I've tested this on a 15,000 row spreadsheet containing just 2 columns. The Adodb method took ~1600 ms to complete the search while the safearray method took ~4200 ms (which includes launching Excel in the background, opening the spreadsheet, and closing up after it's done).

Code: Select all

st := A_TickCount
dataSource := "testfile.xlsx"
objConnection := ComObjCreate("ADODB.Connection")
objRecordSet := ComObjCreate("ADODB.Recordset")
objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" . datasource . "; Extended Properties=""Excel 12.0 xml;HDR=no;IMEX=1"";")
objRecordset.Open("Select * FROM [Sheet1$]", objConnection, 3, 3, 1)
pFields := objRecordset.Fields
arr := []
Loop
	{
	row := []
	Loop, % pFields.Count
		row[A_Index] := pFields.Item(A_Index-1).value
	arr.push(row)
	objRecordset.MoveNext
	} Until objRecordset.EOF
for x,y in Arr
	if (arr[x][1] = "searchterm1" && arr[x][2] = "searchterm2")
		msgbox % x "`n" A_TickCount - st
14.3 & 1.3.7

Supergrover
Posts: 3
Joined: 01 Apr 2023, 23:03

Re: Excel MATCH function fails

Post by Supergrover » 02 Apr 2023, 22:14

Thanks.... but using MS Database/SQL: wouldn't each user of my compiled AHK script also have to down load the MS Database engine? That's probably not feasible, unfortunately.

Any idea if just reading multiple lines from a text file is faster than using Excel (thinking maybe time to 'open' excel is slower than text file)? (I can test at some point but ? you know already). Thanks again for the help :)

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Excel MATCH function fails

Post by flyingDman » 03 Apr 2023, 11:34

Supergrover wrote:
02 Apr 2023, 22:14
Any idea if just reading multiple lines from a text file is faster than using Excel
Yes reading a text file (incl. .csv files) will be much faster than using Excel.
14.3 & 1.3.7

Post Reply

Return to “Ask for Help (v1)”