Regards. I’m using Excel 2008 for a data-intensive exercise, and am discovering the joys of trying to use Applescript to align data across columns. I would have thought this problem would be simple. Perhaps it is – in VB?
To make it simple, Column 1 contains a list of specific CLASSES or IDS that identify data in particular rows. Column 2 contains a subset of Column A’s IDs, aligned with row entries in column 3. The task is to write a script that automates the alignment of Column 3’s data with Column 1’s IDs according to the IDs in column 2.
Well, I almost solved it myself. The setup starts with the expanded meta-index in the first Z rows of column 1, a blank column 2, the actual detailed subset of the meta-index for a particular column of data in column 3, and the initial data corresponding to column 3 in column 4.
The following program searches column 1 for matches of the particular row labels in column 3, and then enters the corresponding data in column 4 into the appropriate row in the blank column.
Of course this can be refined in any number of ways, but the main issue I’ve facing now is to get the thing to halt in a civilized way when it comes to the Z+1th row of column 1. That should be pretty simple.
tell application “Microsoft Excel”
open workbook workbook file name "path:workbookname.xls"
activate
activate object workbook "workbookname.xls"
activate object worksheet "sheet1"
set lastRowNum to first row index of (get end (cell (count of rows of column 1 of active sheet) of column 1 of active sheet) direction toward the top)
select range "A2"
set i to 1
repeat with i from 1 to lastRowNum by 1
set y to get value of cell (i + 1) of column 4
set x to (find (range "b1:b261" of worksheet "Sheet1") what y after "b1" search direction search next)
set z to 0
repeat while z < (get first row index of x)
set cell_source to value of cell ("E" & i + 1)
set value of (get offset range (get address x) column offset 1) to cell_source
set z to first row index of x
end repeat
end repeat
tell application "Microsoft Excel"
activate
set testRange to range (get address of used range of active sheet) of active sheet
repeat with rowNum from 1 to count of rows of testRange
set oneRow to get resize (row rowNum of testRange) column size 3
set getvalue1 to (get value of cell 1 of oneRow)
set getvalue2 to (get value of cell 2 of oneRow)
if getvalue1 is not equal to getvalue2 then
tell cell 2 of oneRow
insert into range (get resize row size 1 column size 2) shift shift down
end tell
end if
end repeat
end tell