Hi,
taffners wrote:
Thank you everyone for your help. I attached my code. It works now but kinda slow. Does anyone have any hints on making it faster.
Important : when you use the find command, it's preferable to use the properties ( look in and look at).
Example : findsearchRangewhatsearchTermlook invalueslook atwhole
Otherwise, it will search according to the criteria of your last find.
Example, if you uncheck 'Find entire cells only', and after that you run the script,
find column 2 what 10.0 --> match 110.0 or 1110.0 or 310.0, .... , because it look at part.
---
set screen updating to false : Yes it improves the speed.
Here are my tests (no duplicate values in the Excel column ) on old computer (Powermac G5) .
set screen updating to false without any activate object command
2000 rows --> 3.7 minutes, your script : 11.5 minutes
4000 rows --> 7.5 minutes, your script : 23 minutes
8000 rows --> 15 minutes, your script : 46 minutes
But, it's faster to search through a list without using the Excel find command, no need to set the screen updating, You can continue working on the workbook or another workbook, this will not disturb the script.
Tests on my script :
2000 rows --> 4 seconds
4000 rows --> 21 seconds
8000 rows --> 1.3 minutes
17000 rows --> 6 minutes
Here is the script :
------------------------------------
main()
display dialog "Done"
onmain()
script o
property patientSNP_list : {}
property relativeSNP_list : {}
property resultsList : {}
property copyAnswerList : {}
onfindLookAtWhole(value) -- case sensitive
set {low, high} to {1, count my relativeSNP_list}
considering case
repeat while low ≠ high
set mid_ to (low + high) div 2
if value is in items low thru mid_ of my relativeSNP_list then
set high to mid_
else
set low to mid_ + 1
end if
end repeat
end considering
if (item low of my relativeSNP_list is value) then
set end of my resultsList to item low of my copyAnswerList
else
set end of my resultsList to {"no"}
end if
endfindLookAtWhole
endscript
--opens File1. Names it patientName
set patientFile to choose file with prompt "Please select the patient Excel file:"
setpatientNametonameof (info forpatientFile)
tell application "Microsoft Excel"
open patientFile
setthepatientFiletoworkbookpatientName
setpatientSheettosheet (nameofactive sheet) ofthepatientFile
end tell
--This Dialog assigns where the data is stored for file1
display dialog "What Column is your patient SNP data of the " & " " & patientName & " " & "excel file?" default answer "" buttons {"submit"} default button 1
setPatientSNPColto (text returnedoftheresult)
--This Dialog assigns what row your data starts at for file1
display dialog "What row does your patient SNP data start in the " & " " & patientName & " " & "excel file?" default answer "" buttons {"submit"} default button 1
setpatientSNPRowto (text returnedoftheresult)
setpatientStartCelltoPatientSNPCol & patientSNPRow
--This Dialog assigns the last row of data for file1
display dialog "What row is the last row of you patient SNP data start of the " & " " & patientName & " " & "excel file?" default answer "" buttons {"submit"} default button 1
setpatientLastRowto (text returnedoftheresult)
setpatientLastCelltoPatientSNPCol & patientLastRow
--This Dialog assigns the location of the data copied from file2
display dialog "What column would you like the results of this program stored in of the" & " " & patientName & " " & "excel file." default answer "" buttons {"submit"} default button 1
setresultsLocationto (text returnedoftheresult)
--opens File2. Names it relativeName
set relativeFile to choose file with prompt "Please select the relative Excel file:"
setrelativeNametonameof (info forrelativeFile)
tell application "Microsoft Excel"
open relativeFile
settherelativeFiletoworkbookrelativeName
setrelativeSheettosheet (nameofactive sheet) oftherelativeFile
end tell
--This dialog assigns the column of data in file2
display dialog "What Column is your relative SNP data of the " & " " & relativeName & " " & "excel file?" default answer "" buttons {"submit"} default button 1
setrelativeSNPColto (text returnedoftheresult)
--This dialog assigns the column of data begining copied to excel file1.
display dialog "What Column contains the data you would like copied to the " & " " & patientName & " " & "excel file in column" & " " & resultsLocation & " " & "?" default answer "" buttons {"submit"} default button 1
setcopyAnswerto (text returnedoftheresult)
tell application "Microsoft Excel" -- get values
seto's patientSNP_listtovalueofrange (PatientSNPCol & patientSNPRow & ":" & PatientSNPCol & patientLastRow) ofpatientSheet
setlastrowtofirst row indexof (lastcellofused rangeofrelativeSheet)
seto's relativeSNP_listtovalueofrange (relativeSNPCol & "1:" & relativeSNPCol & lastrow) ofrelativeSheet
seto's copyAnswerListtovalueofrange (copyAnswer & "1:" & copyAnswer & lastrow) ofrelativeSheet
end tell
settcto (counto's relativeSNP_list)
repeatwithifrom 1 totc --convert list of lists to one list
setitemiofo's relativeSNP_listtoitem 1 ofitemiofo's relativeSNP_list
endrepeat
settcto (counto's patientSNP_list)
repeatwithifrom 1 totc -- AppleScript find method, not the Excel Find
o's findLookAtWhole(item 1 of (itemiofo's patientSNP_list))
endrepeat
tell application "Microsoft Excel" -- set values
setvalueofrange (resultsLocation & patientSNPRow & ":" & resultsLocation & patientLastRow) ofpatientSheettoo's resultsList
end tell
endmain
------------------------------------
If you have lots of rows and you want it to go faster.
I have another script that uses the power of (/usr/bin/awk and his associative arrays) in a do shell script
17000 rows --> awk : 2 seconds, applescript : 6 seconds, total : 8 seconds
34000 rows --> awk : 3 seconds, applescript : 11 seconds, total : 14 seconds
