Really easy to achieve with a script.
--{code}
--[SCRIPT compare 2 columns from 2 Numbers docs]
(*
Enregistrer le script en tant que Script : compare 2 columns from 2 Numbers docs.scpt
déplacer le fichier ainsi créé dans le dossier
<VolumeDeDémarrage>:Utilisateurs:<votreCompte>:Bibliothèque:Scripts:Applications :Numbers:
Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.
Ouvrir les deux documents à comparer.
Ici on suppose que l'on doit comparer le contenu des colonnes 2 de la 1ère table de la 1ère feuille des documents.
Aller au menu Scripts , choisir Numbers puis choisir “compare 2 columns from 2 Numbers docs”
Le script crée un fichier text et l'ouvre avec Numbers.
--=====
L’aide du Finder explique:
L’Utilitaire AppleScript permet d’activer le Menu des scripts :
Ouvrez l’Utilitaire AppleScript situé dans le dossier Applications/AppleScript.
Cochez la case “Afficher le menu des scripts dans la barre de menus”.
Sous 10.6.x,
aller dans le panneau “Général” du dialogue Préférences de l’Éditeur Applescript
puis cocher la case “Afficher le menu des scripts dans la barre des menus”.
--=====
Save the script as a Script: compare 2 columns from 2 Numbers docs.scpt
Move the newly created file into the folder:
<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
Maybe you would have to create the folder Numbers and even the folder Applications by yourself.
Open the two spreadsheets to compare.
Here we assume that we must compare column 2 of table 1 of sheet 1 of both documents.
Go to the Scripts Menu, choose Numbers, then choose “compare 2 columns from 2 Numbers docs”
The script create a temp text file and open it with Numbers.
--=====
The Finder’s Help explains:
To make the Script menu appear:
Open the AppleScript utility located in Applications/AppleScript.
Select the “Show Script Menu in menu bar” checkbox.
Under 10.6.x,
go to the General panel of AppleScript Editor’s Preferences dialog box
and check the “Show Script menu in menu bar” option.
--=====
Yvan KOENIG (VALLAURIS, France)
2012/04/20
*)
--=====
on run
local liste1, liste2, liste3, IF_loc, delim_loc, the_formula, r, tempDoc, nomHaut, nomBas
my activateGUIscripting()
tell application "Numbers"
(*
Extract values from the column to compare.
*)
tell document 1 to tell sheet 1 to tell table 1
set liste1 to value of cells of column 2
end tell
(*
Extract values from the column to compare.
*)
tell document 2 to tell sheet 1 to tell table 1
set liste2 to value of cells of column 2
end tell
end tell
(*
Normalize the lists lengths
*)
if (count liste1) > (count liste2) then
repeat
set end of liste2 to ""
if (count liste2) = (count liste1) then exit repeat
end repeat
else if (count liste1) < (count liste2) then
repeat
set end of liste1 to ""
if (count liste2) = (count liste1) then exit repeat
end repeat
end if
(*
Build a list of couples (val from doc1) tab (val from doc 2)
*)
set liste3 to {}
repeat with r from 1 to count liste1
set end of liste3 to ((item r of liste1) as text) & tab & item r of liste2
end repeat
(*
Build the path to a temporary text file
*)
set tempDoc to ((path to temporary items as text) & "compare.txt")
(*
Write the values to compare in the text file
*)
my writeTo(tempDoc, my recolle(liste3, return), text, false)
(*
Build the comparison formula
*)
set IF_loc to my getLocalizedTabularString("Numbers", "IF")
set delim_loc to my getLocalizedDelimiter()
set the_formula to "=" & IF_loc & "(A=B" & delim_loc & quote & quote & delim_loc & quote & "different" & quote & ")"
tell application "Numbers"
(*
Open the text file
*)
opentempDoc
tell document 1 to tell sheet 1 to tell table 1
(*
Add a new column
*)
add column after last column
set nomHaut to name of first cell of last column
set nomBas to name of last cell
(*
Fill the first cell of the new column
*)
set value of cellnomHaut to the_formula
set selection range to range (nomHaut & " : " & nomBas)
end tell
end tell
(*
Fill the entire column
*)
my selectSubMenu("Numbers", 5, 10, 2) -- Fill Down
end run
--=====
(*
Handler borrowed to Regulus6633 - http://macscripter.net/viewtopic.php?id=36861
*)
on writeTo(targetFile, theData, dataType, apendData)
-- targetFile is the path to the file you want to write
-- theData is the data you want in the file.
-- dataType is the data type of theData and it can be text, list, record etc.
-- apendData is true to append theData to the end of the current contents of the file or false to overwrite it
try
set targetFile to targetFile as text
set openFile to open for accessfiletargetFile with write permission
if not apendData then set eof of openFile to 0
writetheDatatoopenFilestarting ateofasdataType
close accessopenFile
return true
on error
try
close accessfiletargetFile
end try
return false
end try
end writeTo
--=====
on decoupe(t, d)
local oTIDs, l
set oTIDs to AppleScript's text item delimiters
set AppleScript's text item delimiters to 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 to AppleScript's text item delimiters
set AppleScript's text item delimiters to d
set t to "" & l
set AppleScript's text item delimiters to oTIDs
return t
end recolle
--=====
(*
Set the parameter delimiter which must be used in Numbers formulas
*)
on getLocalizedDelimiter()
if character 2 of (0.5 as text) is "." then
return ","
else
return ";"
end if
end getLocalizedDelimiter
--=====
on get_iWorkNum(a)
local verNum
tell application a to set verNum to item 1 of my decoupe(get version, ".")
if (a is "Numbers" and verNum is "2") or (a is "Pages" and verNum is "4") then
return "09"
else
return "11"
end if
end get_iWorkNum
--=====
on getLocalizedName(a, x, f)
tell application a to return localized string x from table "Localizable" in bundle file f
end getLocalizedName
--=====
(*
Useful to get function’s localized name if we need to build formulas
examples:
set OFFSET_loc to my getLocalizedTabularString("Numbers", "OFFSET")
set ADDRESS_loc to my getLocalizedTabularString("Numbers", "ADDRESS")
set INDIRECT_loc to my getLocalizedTabularString("Numbers", "INDIRECT")
set Unnamed_Table_loc to my getLocalizedTabularString("Numbers", "Unnamed Table")
Requires :
decoupe()
get_iWorkNum()
getLocalizedName()
*)
on getLocalizedTabularString(theApp, x)
local path2app
activateapplicationtheApp
tell application "System Events"
set path2app to (application file of (get application process theApp) as text)
exists folder (path2app & "Contents:Frameworks:")
end tell
if result then
path2app & "Contents:Frameworks:SFTabular.framework:Versions:A:Resources:"
else
(path to application support as text) & "iWork '" & my get_iWorkNum(theApp) & ":Frameworks:SFTabular.framework:Versions:A:Resources:"
end if
return my getLocalizedName(theApp, x, result)
end getLocalizedTabularString
--=====
on activateGUIscripting()
(* to be sure than GUI scripting will be active *)
tell application "System Events"
if not (UI elements enabled) then set (UI elements enabled) to true
end tell
end activateGUIscripting
--=====
(*
my selectSubMenu("Pages",6, 4, 26)
==== Uses GUIscripting ====
*)
on selectSubMenu(theApp, mt, mi, ms)
activateapplicationtheApp
tell application "System Events" to tell application process theApp to tell menu bar 1 to ¬
tell menu bar item mt to tell menu 1 to tell menu item mi to tell menu 1 to click menu item ms
end selectSubMenu
--=====
--[/SCRIPT]
--{code}
Yvan KOENIG (VALLAURIS, France) vendredi 20 avril 2012
iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.3
My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k
