Aligning column entries in Excel

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.

EG: Before script application:

A A 10
B B 3
C D 5
D F 4

Desired After:

A A 10
B B 3
D D 5
F F 4

I’m grateful for any suggestions.

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 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

end tell

Thanks in advance for any additional inputs!

You could try This

tell application "Microsoft Excel"
	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