Hi,
Is there a way, using Applescript, to count how many times values appear in column A? (could use Numbers or Excel)
I have a long list wherein the same three names appear regularly and I would need to count them each week.
So, like,
“Adam = 07”
“Jane = 04”
“Hannah = 11”
On my side I would not use AppleScript but formulas using COUNTIF but here is a code doing what I understood in your message.
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
set theValues to value of cells of column 1
end tell
set {nbAdam, nbJane, nbHannah} to {0, 0, 0}
repeat with aValue in theValues
set aValue to aValue as text
if aValue is "Adam" then
set nbAdam to nbAdam + 1
else if aValue is "Jane" then
set nbJane to nbJane + 1
else if aValue is "Hannah" then
set nbHannah to nbHannah + 1
end if
end repeat
{nbAdam, nbJane, nbHannah}
You may use an alternate scheme :
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
set theValues to value of cells of column 1
end tell
set valuesAsText to my recolle(theValues, linefeed)
set nbAdam to (count my decoupe(valuesAsText, "Adam")) - 1
set nbJane to (count my decoupe(valuesAsText, "Jane")) - 1
set nbHannah to (count my decoupe(valuesAsText, "Hannah")) - 1
{nbAdam, nbJane, nbHannah}
#=====
on decoupe(t, d)
local oTIDs, l
set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
set l to text items of t
set AppleScript's text item delimiters to oTIDs
return l
end decoupe
#=====
on recolle(l, d)
local oTIDs, t
set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
set t to l as text
set AppleScript's text item delimiters to oTIDs
return t
end recolle
#=====
You may choose to use modern features and use :
use scripting additions
use framework "Foundation"
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
set theValues to value of cells of column 1
end tell
set valuesAsText to my concatList:theValues usingString:linefeed
set nbAdam to (count (my split:valuesAsText usingString:"Adam")) - 1
set nbJane to (count (my split:valuesAsText usingString:"Jane")) - 1
set nbHannah to (count (my split:valuesAsText usingString:"Hannah")) - 1
{nbAdam, nbJane, nbHannah}
#=====
on split:sourceString usingString:d1
set sourceString to current application's NSString's stringWithString:sourceString
return (sourceString's componentsSeparatedByString:d1) as list
end split:usingString:
on concatList:theList usingString:d1
set anArray to current application's NSArray's arrayWithArray:theList
return (anArray's componentsJoinedByString:d1) as text
end concatList:usingString:
#=====
Yvan KOENIG running Sierra 10.12.0 in French (VALLAURIS, France) mardi 25 octobre 2016 16:07:51
Awesome, that works perfectly! Thank you!
Hi Yvan.
When using the “coerce to text and count the text items” method, there’s a possibility that the search item may be a substring of one or more of the other items, producing false hits.
A way to avoid this is to bracket both the search item and each item individually in the coerced text with the linefeed:
-- tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
-- set theValues to value of cells of column 1
-- end tell
set theValues to {"John", "Fred", "Adam", "Adam", "Aardvark", "Janet", "Jane", "Mary-Jane"}
set LFLF to {linefeed, linefeed}
set valuesAsText to recolle(LFLF, recolle(theValues, recolle(LFLF, "")))
set nbAdam to (count decoupe(valuesAsText, recolle(LFLF, "Adam"))) - 1
set nbJane to (count decoupe(valuesAsText, recolle(LFLF, "Jane"))) - 1
set nbHannah to (count decoupe(valuesAsText, recolle(LFLF, "Hannah"))) - 1
{nbAdam, nbJane, nbHannah}
#=====
on decoupe(t, d)
local oTIDs, l
set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
set l to text items of t
set AppleScript's text item delimiters to oTIDs
return l
end decoupe
#=====
on recolle(l, d)
local oTIDs, t
set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
set t to l as text
set AppleScript's text item delimiters to oTIDs
return t
end recolle
#=====
Thank’s Nigel.
I will add the tip to my library.
Yvan KOENIG running Sierra 10.12.1 in French (VALLAURIS, France) vendredi 28 octobre 2016 10:48:40
Here for fun is another ASObjC method:
use AppleScript version "2.4"
use framework "Foundation"
-- tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
-- set theValues to value of cells of column 1
-- end tell
set theValues to {"John", "Fred", "Adam", "Adam", "Aardvark", "Janet", "Jane", "Mary-Jane"}
set nbAdam to countOccurrences(theValues, "Adam")
set nbJane to countOccurrences(theValues, "Jane")
set nbHannah to countOccurrences(theValues, "Hannah")
{nbAdam, nbJane, nbHannah}
on countOccurrences(aList, aString)
set anArray to current application's class "NSArray"'s arrayWithArray:(aList)
set aPredicate to current application's class "NSPredicate"'s predicateWithFormat:("self = %@") argumentArray:({aString})
return (anArray's filteredArrayUsingPredicate:(aPredicate))'s |count|()
end countOccurrences
or with AST:
set theValues to {"John", "Fred", "Adam", "Adam", "Aardvark", "Janet", "Jane", "Mary-Jane"}
set nbAdam to countOccurrences(theValues, "Adam")
set nbJane to countOccurrences(theValues, "Jane")
set nbHannah to countOccurrences(theValues, "Hannah")
on countOccurrences(lst, val)
return count of (AST copy list lst matching regex "^" & val & "$")
end countOccurrences
And another:
use AppleScript version "2.4"
use framework "Foundation"
-- tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
-- set theValues to value of cells of column 1
-- end tell
set theValues to {"John", "Fred", "Adam", "Adam", "Aardvark", "Janet", "Jane", "Mary-Jane"}
set nbAdam to countOccurrences(theValues, "Adam")
set nbJane to countOccurrences(theValues, "Jane")
set nbHannah to countOccurrences(theValues, "Hannah")
{nbAdam, nbJane, nbHannah}
on countOccurrences(aList, aString)
set countedSet to current application's NSCountedSet's setWithArray:aList
return countedSet's countForObject:aString
end countOccurrences
Edit: Ideally, I suppose, the set would only be created once:
use AppleScript version "2.4"
use framework "Foundation"
-- tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
-- set theValues to value of cells of column 1
-- end tell
set theValues to {"John", "Fred", "Adam", "Adam", "Aardvark", "Janet", "Jane", "Mary-Jane"}
set countedSet to current application's NSCountedSet's setWithArray:(theValues)
set nbAdam to countedSet's countForObject:("Adam")
set nbJane to countedSet's countForObject:("Jane")
set nbHannah to countedSet's countForObject:("Hannah")
{nbAdam, nbJane, nbHannah}
Or:
use AppleScript version "2.4"
use framework "Foundation"
-- tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
-- set theValues to value of cells of column 1
-- end tell
set theValues to {"John", "Fred", "Adam", "Adam", "Aardvark", "Janet", "Jane", "Mary-Jane"}
tell (current application's NSCountedSet's setWithArray:(theValues)) to return {its countForObject:("Adam"), its countForObject:("Jane"), its countForObject:("Hannah")}
Yep, that’s more efficient.
The other thing worth noting is that, unlike the TIDs method, these examples are all case-sensitive.
For the record: AST is case insensitive by default.
Hmmm. I see this in the dictionary:
And I have to say, although AppleScript generally favors case-insensitive comparisons, I wonder whether it makes sense as a default for regex purely because I don’t think it’s the default in any other regex.
Here is a case-insensitive version.
use AppleScript version "2.4"
use framework "Foundation"
-- tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
-- set theValues to value of cells of column 1
-- end tell
set theValues to {"John", "Fred", "Adam", "Adam", "Aardvark", "Janet", "Jane", "Mary Jane"}
set listOfStrings to {}
repeat with aString in theValues
set oneString to (current application's NSString's stringWithString:aString)
set end of listOfStrings to (oneString's uppercaseString()) as text
end repeat
set countedSet to current application's NSCountedSet's setWithArray:(listOfStrings)
set nbAdam to countedSet's countForObject:(my makeCaps:"Adam")
set nbJane to countedSet's countForObject:(my makeCaps:"Jane")
set nbHannah to countedSet's countForObject:(my makeCaps:"Hannah")
set nbMaryJane to countedSet's countForObject:(my makeCaps:"Mary Jane")
{nbAdam, nbJane, nbHannah, nbMaryJane} --> {2, 1, 0, 1}
on makeCaps:aString
set oneString to (current application's NSString's stringWithString:aString)
return (oneString's uppercaseString()) as text
end makeCaps:
I wished to skip the auxiliary list so I tested :
use AppleScript version "2.4"
use framework "Foundation"
-- tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
-- set theValues to value of cells of column 1
-- end tell
set theValues to {"John", "Fred", "Adam", "Adam", "Aardvark", "Janet", "Jane", "Mary Jane"}
set countedSet to current application's NSCountedSet's setWithArray:({})
repeat with aString in theValues
set oneString to (current application's NSString's stringWithString:aString)
set countedSet to (countedSet's setByAddingObject:(oneString's uppercaseString()))
end repeat
set nbAdam to countedSet's countForObject:(my makeCaps:"Adam")
set nbJane to countedSet's countForObject:(my makeCaps:"Jane")
set nbHannah to countedSet's countForObject:(my makeCaps:"Hannah")
set nbMaryJane to countedSet's countForObject:(my makeCaps:"Mary Jane")
{nbAdam, nbJane, nbHannah, nbMaryJane} --> {1, 1, 0, 1}
on makeCaps:aString
set oneString to (current application's NSString's stringWithString:aString)
return (oneString's uppercaseString()) as text
end makeCaps:
And discovered (maybe it’s just hat I forgot it) that setByAdding doesn’t insert several occurrences of a given object.
Yvan KOENIG running Sierra 10.12.1 in French (VALLAURIS, France) dimanche 30 octobre 2016 15:42:09
One would hope case differences wouldn’t an issue with names taken from a spreadsheet… 
Anyway, here’s another version:
use AppleScript version "2.4"
use framework "Foundation"
-- tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
-- set theValues to value of cells of column 1
-- end tell
set theValues to {"John", "Fred", "Adam", "Adam", "Aardvark", "Janet", "Jane", "Mary-Jane"}
set theValues to ((current application's class "NSArray"'s arrayWithArray:(theValues))'s componentsJoinedByString:(linefeed))'s uppercaseString()'s componentsSeparatedByString:(linefeed)
tell (current application's class "NSCountedSet"'s setWithArray:(theValues)) to return {its countForObject:("ADAM"), its countForObject:("JANE"), its countForObject:("HANNAH")}
Note taken, I see that AST copy list has the right documentation but forgot to update the AST find regex command. Will be updated in next version.
By defaults the regex will be executed with the extended, enhanced and ignore case options in later 1.x versions of AST. In AppleScript “hello” is the same as “Hello” and the “other regex” is of course used by programming languages where the default is an case sensitive match. In context of to keep things simple as possible that it is better to have the most used and obvious options turned on. So therefore we (Nigel and I) thought it would be best to turn these three options on by default.
Or:
set theValues to (current application's class "NSArray"'s arrayWithArray:(theValues))'s valueForKey:"uppercaseString"
And the timing obsessive in me says that using lowercase rather than uppercase might shave off a nanosecond somewhere
Aha!
Smaller letters, I suppose. :lol:
More the thrill of cutting the big ones down to size
Counted sets are mutable, so you can do this:
repeat with aString in theValues
set oneString to (current application's NSString's stringWithString:aString)
countedSet's addObject:(oneString's uppercaseString())
end repeat
But any time you can replace a repeat loop, such as using valueForKey: above, it is usually better in terms of performance.
Then you have to try assembler one day