I have 500 cells of column data that I need to format so that it conforms exactly to data that has already been entered into a separate excel worksheet. For instance, the sheet that I am working on may have “Barclays” as the current cell data, however the database requires that this cell data be “Barclays Bank”. The proper formatting appears alphabetically in the master sheet I mentioned early.
My question is would it be possible to create a script that would work down the column of my spreadsheet, read the data, go to the master spreadsheet and search for a matching word (in this case, Barclays), copy the correct data and paste it into the original spreadsheet?
What version of Excel? If you use 2004, AppleScript can do it, no problem. If you are using an older version, or you’re not an AppleScripter, you might wish to consider writing a MS VBA macro instead. There are books, MS help files, and web sites that deal exclusively with writing VBA. If you script and it’s Excel 2004, you’ll find that MS has done a really nice job implementing scripting in this latest version.
Yes, I am using Excel 2004. It’s great to hear that it would be an easy task to accomplish with applescript. Can you point me in the right direction to learning how i can do it? Any suggestions? thanks.
set master_file to choose file with prompt “Master File.”
set original_file to choose file with prompt “Original File.”
tell me to set master_data to loadMasterFile(master_file)
tell application “Microsoft Excel”
activate
open original_file
repeat with i from 1 to 500
set value of cell 1 of row i of active sheet to getCorrectData(master_data, value of cell 1 of row i of active sheet as string) of me
end repeat
close every workbook saving yes
end tell
on getCorrectData(master_data, the_string)
repeat with i from 1 to count of master_data
if (item i of master_data as string) contains the_string then return item i of master_data
end repeat
end getCorrectData
– this subroutine loads the correct data into master_data list - the function assumes that the correct data is in column A
on loadMasterFile(master_file)
set master_data to {}
tell application “Microsoft Excel”
activate
open master_file
repeat with i from 1 to 500
copy value of cell 1 of row i of active sheet to the end of master_data
end repeat
close every workbook saving no
end tell
return master_data
end loadMasterFile