Excel Find closest Number in a range

I am trying to find an AppleScript to look for the closest number in a range. Have searched the web and what I have found suggests I have to first turn my range into a list and obviously therefore my search value into a string. However I want the closest value which may of course not be the exact equivalent. The range I need to search could be hundreds of cells .

Thanks

Peter

Failed to add code I found the basis of on the web that works with text but not numbers and also only for exact matches


tell application "Microsoft Excel"
	set SymbolList to range ("A1:A15")
	set Symbol to find SymbolList what "MAS" look at value
	set RowCnt to first row index of Symbol
	set Fullname to value of range ("B" & RowCnt as text)
	display dialog Fullname
end tell

I’m not entirely sure what exactly you mean by “closest number in a range”, whether that is the closest value numerically or the closest cell in the spreadsheet that contains a numeric value, but…

This code will take a single column or single row range of numbers and return the numeric value that is closest to the given target number.


use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use scripting additions

-- classes, constants, and enums used
property NSArray : a reference to current application's NSArray

on abs(num)
	if num < 0 then
		return -num
	else
		return num
	end if
end abs

to findClosestValueTo:numericValue within:rangeOfValues
	set closestVal to missing value
	
	-- collapse the 2d range given to us by Excel into a 1d array
	set rngArray to ((NSArray's arrayWithArray:rangeOfValues)'s valueForKeyPath:"@unionOfArrays.self") as list
	
	set minAbsVal to missing value
	set idx to missing value
	repeat with i from 1 to count of rngArray
		set absVal to abs((item i of rngArray) - numericValue)
		if minAbsVal is missing value or absVal is less than minAbsVal then
			set idx to i
			set minAbsVal to absVal
		end if
	end repeat
	
	if idx is not missing value then
		set closestVal to (item idx of rngArray)
	end if
	
	return closestVal as integer
end findClosestValueTo:within:

You can call it like so:


on run
	
	tell application "Microsoft Excel"
		
		tell active sheet
			
			-- get just the column cells that actually contain data
			set rng to intersect range1 (used range) range2 "$A:$A"
			set rngVals to string value of rng
			
			-- if we are looking at just a single cell, just bail
			-- because it doesn't make much sense to continue
			if class of rngVals is string then
				error number -128
			end if
			
		end tell
		
	end tell
	
	set closestVal to my findClosestValueTo:319 within:rngVals
	
end run

Sorry for not acknowledging your suggestion earlier. Have not had a chance to work on your solution yet. However to be clear I am looking for the closest value in a column. Just looking at your solution I am unclear as to how I pass the column with the values to be checked . I have struggled with this issue and in the past have resorted to calling a VBA macro. It would be great to have the entire sequence in AppleScript.

Thanks very much for taking the time to help

Peter

The second code snippet I posted shows how to pass in the column you want to check. I would not suggest passing an entire column, because that includes all the blank cells outside your used range. Instead, get just the intersection of the used range with the column, like so:

set rng to intersect range1 (used range) range2 "$A:$A"

(Specifying range2 however you want to; I just went with the range’s address for simplicity but your code may use range object or whatever.)

The routine I gave you actually accepts as a parameter the string value of the range, so you get it with this:

set rngVals to string value of rng

Really, I should have put the check to test that we have a single cell inside the handler, as well as making sure we only have a single column/row of values, but that can be left as an exercise for the user, I guess.

Again my apologies for the delay in replying, however I am afraid your solutions is beyond me. I think it is because I cannot figure out how to get the NSArray. The set rng to intersect range1 (used range) range2 of “B:B”) certainly provides a list of everything in the Column B all of which other than the header are numbers. I do not understand the 319 in the call to the routine findclosestvalue.

In case I have not explained my issue properly, as an example the line referred to above produces

“tell application “Microsoft Excel”
intersect range1 used range of active sheet range2 “$B:$B”
→ range “‘[DividendCapture 125133.xlsm.xlsm]FAST’!$B$1:$B$14”
get string value of range “‘[DividendCapture 125133.xlsm.xlsm]FAST’!$B$1:$B$14”
→ {{“Strike”}, {”$25.00"}, {“$27.50”}, {“$30.00”}, {“$32.50”}, {“$35.00”}, {“$37.50”}, {“$40.00”}, {“$42.50”}, {“$45.00”}, {“$47.50”}, {“$50.00”}, {“$52.50”}, {“$55.00”}}"

From that I want get the closest value to say 34.75 which in this case would of course be $35.00

Thank you agin for taking the time my very clumsy solution is working for the time being.

319 is just the value I used as an example of how to call the function. It’s analogous to the 34.75 you are looking for in your example.

Since you are looking for a number in a list of dollar amounts, the function I gave you needs to be tweaked a bit to remove the $ first:


to findClosestValueTo:numericValue within:rangeOfValues
	set closestVal to missing value
	
	-- if we are looking at just a single cell, just bail
	-- because it doesn't make much sense to continue
	if class of rangeOfValues is string then
		error number -128
	end if
	
	-- if we are dealing with more than one column, bail
	if (count of item 1 of rangeOfValues) is greater than 1 then
		error number -128
	end if
	
	-- collapse the 2d range given to us by Excel into a 1d array
	set rngArray to ((NSArray's arrayWithArray:rangeOfValues)'s valueForKeyPath:"@unionOfArrays.self") as list
	
	set minAbsVal to missing value
	set idx to missing value
	repeat with i from 1 to count of rngArray
		set val to (NSString's stringWithString:(item i of rngArray))
		set val to (val's stringByReplacingOccurrencesOfString:"$" withString:"" options:NSCaseInsensitiveSearch range:{0, val's |length|()}) as text
		set absVal to abs(val - numericValue)
		if minAbsVal is missing value or absVal is less than minAbsVal then
			set idx to i
			set minAbsVal to absVal
		end if
	end repeat
	
	if idx is not missing value then
		set closestVal to (item idx of rngArray)
	end if
	
	-- this is to trim off the trailing whitespace Excel insists on putting in there
	set closestVal to ((NSString's stringWithString:closestVal)'s stringByTrimmingCharactersInSet:(NSCharacterSet's whitespaceAndNewlineCharacterSet())) as text
	
	return closestVal
end findClosestValueTo:within:

The header will be a problem too, but I would strip it out before you pass your range into the function, which you can do by modifying the intersect command to Excel.


on run
	
	tell application "Microsoft Excel"
		
		tell active sheet
			
			-- get just the column cells that actually contain data
			set rng to intersect range1 (used range) range2 "$B:$B" range3 (get offset used range row offset 1)
			set rngVals to string value of rng
			
			-- if we are looking at just a single cell, just bail
			-- because it doesn't make much sense to continue
			if class of rngVals is string then
				error number -128
			end if
			
		end tell
		
	end tell
	
	set closestVal to my findClosestValueTo:34.75 within:rngVals
	
end run

Given the sample data you provided, this will return “$35.00”

Thank you once again. however it is still not working it stalls the 1st time the NSArray is used this is the reponse.

tell application “Microsoft Excel”
get offset used range of active sheet row offset 1
→ range “‘[DividendCapture 125133.xlsm.xlsm]FAST’!$A$2:$G$15”
intersect range1 used range of active sheet range2 “$B:$B” range3 range “‘[DividendCapture 125133.xlsm.xlsm]FAST’!$A$2:$G$15”
→ range “‘[DividendCapture 125133.xlsm.xlsm]FAST’!$B$2:$B$14”
get string value of range “‘[DividendCapture 125133.xlsm.xlsm]FAST’!$B$2:$B$14”
→ {{“$25.00”}, {“$27.50”}, {“$30.00”}, {“$32.50”}, {“$35.00”}, {“$37.50”}, {“$40.00”}, {“$42.50”}, {“$45.00”}, {“$47.50”}, {“$50.00”}, {“$52.50”}, {“$55.00”}}
Result:
error “The variable NSArray is not defined.” number -2753 from “NSArray”

Just to sure I copied your script correctly, it is below, I added AppelScript version, framework and scripting additions as you had them originally.


use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use scripting additions
on run
	
	tell application "Microsoft Excel"
		
		tell active sheet
			
			-- get just the column cells that actually contain data
			set rng to intersect range1 (used range) range2 "$B:$B" range3 (get offset used range row offset 1)
			set rngVals to string value of rng
			
			-- if we are looking at just a single cell, just bail
			-- because it doesn't make much sense to continue
			if class of rngVals is string then
				error number -128
			end if
			
		end tell
		
	end tell
	
	set closestVal to my findClosestValueTo:34.75 within:rngVals
	
end run

to findClosestValueTo:numericValue within:rangeOfValues
	set closestVal to missing value
	
	-- if we are looking at just a single cell, just bail
	-- because it doesn't make much sense to continue
	if class of rangeOfValues is string then
		error number -128
	end if
	
	-- if we are dealing with more than one column, bail
	if (count of item 1 of rangeOfValues) is greater than 1 then
		error number -128
	end if
	
	-- collapse the 2d range given to us by Excel into a 1d array
	set rngArray to ((NSArray's arrayWithArray:rangeOfValues)'s valueForKeyPath:"@unionOfArrays.self") as list
	
	set minAbsVal to missing value
	set idx to missing value
	repeat with i from 1 to count of rngArray
		set val to (NSString's stringWithString:(item i of rngArray))
		set val to (val's stringByReplacingOccurrencesOfString:"$" withString:"" options:NSCaseInsensitiveSearch range:{0, val's |length|()}) as text
		set absVal to abs(val - numericValue)
		if minAbsVal is missing value or absVal is less than minAbsVal then
			set idx to i
			set minAbsVal to absVal
		end if
	end repeat
	
	if idx is not missing value then
		set closestVal to (item idx of rngArray)
	end if
	
	-- this is to trim off the trailing whitespace Excel insists on putting in there
	set closestVal to ((NSString's stringWithString:closestVal)'s stringByTrimmingCharactersInSet:(NSCharacterSet's whitespaceAndNewlineCharacterSet())) as text
	
	return closestVal
end findClosestValueTo:within:

Some required words were missing.
The code below is supposed to be complete.


use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use scripting additions
on run
	
	tell application "Microsoft Excel"
		
		tell active sheet
			
			-- get just the column cells that actually contain data
			set rng to intersect range1 (used range) range2 "$B:$B" range3 (get offset used range row offset 1)
			set rngVals to string value of rng
			
			-- if we are looking at just a single cell, just bail
			-- because it doesn't make much sense to continue
			if class of rngVals is string then
				error number -128
			end if
			
		end tell
		
	end tell
	
	set closestVal to my findClosestValueTo:34.75 within:rngVals
	
end run

to findClosestValueTo:numericValue within:rangeOfValues
	set closestVal to missing value
	
	-- if we are looking at just a single cell, just bail
	-- because it doesn't make much sense to continue
	if class of rangeOfValues is string then
		error number -128
	end if
	
	-- if we are dealing with more than one column, bail
	if (count of item 1 of rangeOfValues) is greater than 1 then
		error number -128
	end if
	
	-- collapse the 2d range given to us by Excel into a 1d array
	set rngArray to ((current application's NSArray's arrayWithArray:rangeOfValues)'s valueForKeyPath:"@unionOfArrays.self") as list
	
	set minAbsVal to missing value
	set idx to missing value
	repeat with i from 1 to count of rngArray
		set val to (current application's NSString's stringWithString:(item i of rngArray))
		set val to (val's stringByReplacingOccurrencesOfString:"$" withString:"" options:NSCaseInsensitiveSearch range:{0, val's |length|()}) as text
		set absVal to abs(val - numericValue)
		if minAbsVal is missing value or absVal is less than minAbsVal then
			set idx to i
			set minAbsVal to absVal
		end if
	end repeat
	
	if idx is not missing value then
		set closestVal to (item idx of rngArray)
	end if
	
	-- this is to trim off the trailing whitespace Excel insists on putting in there
	set closestVal to ((current application's NSString's stringWithString:closestVal)'s stringByTrimmingCharactersInSet:(current application's NSCharacterSet's whitespaceAndNewlineCharacterSet())) as text
	
	return closestVal
end findClosestValueTo:within:

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) samedi 18 juillet 2020 21:44:58

You left out this:


-- classes, constants, and enums used
property NSArray : a reference to current application's NSArray

on abs(num)
	if num < 0 then
		return -num
	else
		return num
	end if
end abs

And I forgot to include this when I posted the second iteration:


property NSString : a reference to current application's NSString
property NSCharacterSet : a reference to current application's NSCharacterSet
property NSCaseInsensitiveSearch : a reference to 1

Thank you both, it now seems to be working, I have one last request it rounds up or down how can I make it round up or down. Most times it would need to be up.

By the way the references

property NSString : a reference to current application’s NSString
property NSCharacterSet : a reference to current application’s NSCharacterSet
property NSCaseInsensitiveSearch : a reference to 1

we’re not in the 1st iteration and for that matter not in Yvan’s so when I ran his version of the code it failed at the NSCaseInsentiveSearch.

Peter

So you’re saying that, for instance, 34.5 should match to either 34 or 35, depending on whether you want to round up or down?

EDIT: Nevermind, that will fail under some circumstances. Let me fiddle with it a bit more.

As for this:

Right, because they weren’t needed in the first iteration. And, as I mentioned, I forgot to include them when I pasted in the second iteration.

They aren’t in Yvan’s code because he uses the other way of using ASOC objects by inlining the current application’s stuff rather than setting up separate properties. But he did forget the one for NSCaseInsensitiveSearch.

Glad it’s working for you now.