Check if at Item in an Excel range is contained in another range

Scripting Forums AppleScript | Mac OS X
#1

The only way I have been able to get the answer to my question is to built the range dynamically. Trying to pick the ranges up from an Excel worksheet does not work. The script below shows both approaches and this is the log from running that script which shows identical results but as I said will not work when the test in the script is applied if it is relying on the values from the range.

The log results

(false)
(CG, HE, FIX, FLIR, HE)–A3:A7
(HE, CG, FLIR, HE, CRAI, AFL, EMR)–C3:C9
(true)
(CG, HE, FIX, FLIR, HE)
(HE, CG, FLIR, HE, CRAI, AFL, EMR)

tell application "Microsoft Excel"
	set Firstlist to value of range ("A3:A7")
	set Secondlist to value of range ("C3:C9") 
end tell
set Test to Secondlist contains item 1 of Firstlist
log Test
log Firstlist
log Secondlist

set Firstlist to {}
set Secondlist to {}
tell application "Microsoft Excel"
	set i to 3
	repeat 5 times
		set end of Firstlist to value of range ("A" & i)
		set i to i + 1
	end repeat
	set i to 3
	repeat 7 times
		set end of Secondlist to value of range ("C" & i)
		set i to i + 1
	end repeat
end tell
set Test to Secondlist contains item 1 of Firstlist
log Test
log Firstlist
log Secondlist

I have tried coercing both list into lists and strings , no luck , using strings picks up combination in the 1st part of the script but that does not work as for instance if I look for CGF in the second string it finds it .

As I said the second way works but is longer and will take more time I imagine if I have very big lists.

Thanks as always for any suggestions.

#2

Wouldn’t this be a prime candidate for using ‘reference to’? As per the ASLG > Class Reference > List…

https://developer.apple.com/library/archive/documentation/AppleScript/Conceptual/AppleScriptLangGuide/reference/ASLR_classes.html#//apple_ref/doc/uid/TP40000983-CH1g-BBCDBHIE

That said, I can’t help but think that it would be more efficient to use Excel’s capabilities. There are probably a couple of approaches but a simple one would be to put a ‘countif’ formula in a cell in the spreadsheet, for example:

tell application "Microsoft Excel"
	
	set formula of cell "B3" to "=COUNTIF(C3:C9,A3)"
	
	set Test to value of cell "B3" > 0
	--> true

end tell

Excel would handle large lists rather effortlessly. I just queried an 8500 item list and it was the same speed as your list of 7 items.

An alternative might be to include a macro in the spreadsheet that does the countif,

[format]Application.WorksheetFunction.CountIf(ws.Range(“C3:C9”), ws.Range(“A3”))[/format]

… and then do a ‘run vb macro’, similar to how ‘do shell script’ operates.

An example can be found here:
https://www.exceldome.com/solutions/if-a-range-contains-a-specific-value/

#3

Hi, again.

I tested right now on my Mac. The solution is:


tell application "Microsoft Excel"
	set Firstlist to value of range ("A3:A7")
	set Secondlist to value of range ("C3:C9")
end tell
set Test to Secondlist contains {item 1 of Firstlist} -- THIS: parentheses is required
#4

HI KniazidisR

Once again thank you, know I would never have got to that solution on my own. Simple and very compact.

Moxkman

Thank you for taking the trouble, I will look at using the class reference. The Excel approach is very neat, there are a lot of other steps in my routine and I think using all Excel commands would increase the time. Have tried to avoid invoking VBA solutions, that I used to use as I now seem to run into problems running Excel 16.48 on Catalina.

#5

Glad you found a solution.