Hello. (after posting i realized the subject heading was misleading… should have been “how can i get…” sorry if anybody opened it hoping for an answer)
I’ve been working on a script and seem to have hit a wall with the final piece of it.
I am taking data from excel, processing it in bbedit, bringing it back into excel, sorting by the column i just pasted into, and pasting the relevant data into the previous column.
the problem i’m having is how to copy the cells in column C that actually have data in them and paste them to column B without erasing data from those cells that don’t have corresponding data in column C.
example…
A B C
1 2 3
1 2 3
1 2 3
1 2
1 2
becomes…
A B C
1 3 3
1 3 3
1 3 3
1 2
1 2
at which point i will delete column C.
here’s what i have working so far (i’m including the BBedit content as well). I’m sure it can be cleaned up a bit, but right now i’m just trying to get it functional.
any help would be greatly appreciated. The things i’ve found online that SHOULD work don’t seem to…
tell application "Microsoft Excel"
open text file filename ¬
"Macintosh HD:Users:me:testfile.csv" data type delimited with comma
copy range column "V:V"
end tell
tell application "BBEdit"
activate
make new text document
paste
replace "-" using "," searching in text 1 of text document 1 options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}
replace "win," using "win,\\r" searching in text 1 of text document 1 options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}
replace "pass," using "\\rpass," searching in text 1 of text document 1 options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}
process lines containing text 1 of text document 1 matching string "win," output options {deleting matched lines:true, reporting results:false}
process lines containing text 1 of text document 1 matching string "pass," output options {deleting matched lines:true, reporting results:false}
replace ", " using "" searching in text 1 of text document 1 options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}
replace "," using "\\r," searching in text 1 of text document 1 options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}
process lines containing text 1 of text document 1 matching string "," output options {deleting matched lines:true, reporting results:false}
select text 1 of text window 1
copy selection
close active document of window 1 saving no
end tell
tell application "Microsoft Excel"
activate
insert into range column "C:C" of worksheet 1 of active workbook shift shift to right
select range "c1" of worksheet 1 of active workbook
paste worksheet sheet 1 of active workbook destination range "C:C"
sort range "C1" order1 sort ascending key1 column "C:C" header header yes without match case
end tell