Another Excel 2008 problem. comparing and moving data.

Scripting Forums AppleScript | Mac OS X
,
#1

Any help on this one would be very much appreciated as it’s completely out of my depth and is the final piece of a really big puzzle.

I have a scenario where i need Excel 2008 to compare the contents of each cell in COLUMN E with the cell below it. If that matches then i need to compare the contents of each cell’s COLUMN B data. If the lower cell of the pair’s value is one minute higher than the upper row then i need to move the data from COLUMN J,K,L in the lower row to COLUMN M,N,O in the upper row.

Occasionally there is a THIRD row that needs to have data moved to the first row. in this case the values in CELLs E and E+2 would still match, but the contents of CELLs B & B+2 would be two minutes apart.

in cases like this i have to move the data from COLUMN J,K,L up one row and into COLUMN M,N,O. Worst case scenario, they have a third stop (2 minutes from original time) so i have to move the data from COLUMN J,K,L up two rows and into COLUMN P,Q,R.

EXAMPLE:
With two addresses the relevent fields look like this…

|A0|B000|C0|D0|E00000|F0|G0|H0|I0|J0000|K0000|L0000|M0|N0|O0|P0|Q0|R0|
|A1|7:00|C1|D1|555666|F1|G1|H1|I1|ADDR1|CITY1|UNIT1|M1|N1|O1|P1|Q1|R1|
|A2|7:01|C2|D2|555666|F2|G2|H2|I2|ADDR2|CITY2|UNIT2|M2|N2|O2|P2|Q2|R2|

but needs to look like this…

|A0|B000|C0|D0|E00000|F0|G0|H0|I0|J0000|K0000|L0000|M0000|N0000|O0000|P0|Q0|R0|
|A1|7:00|C1|D1|555666|F1|G1|H1|I1|ADDR1|CITY1|UNIT1|ADDR2|CITY2|UNIT2|P1|Q1|R1|
|A2|7:01|C2|D2|555666|F2|G2|H2|I2|-----|-----|-----|-----|-----|-----|P2|Q2|R2|

With three addresses the relevant fields look like this…

|A0|B000|C0|D0|E00000|F0|G0|H0|I0|J0000|K0000|L0000|M0|N0|O0|P0|Q0|R0|
|A1|7:00|C1|D1|555666|F1|G1|H1|I1|ADDR1|CITY1|UNIT1|M1|N1|O1|P1|Q1|R1|
|A2|7:01|C2|D2|555666|F2|G2|H2|I2|ADDR2|CITY2|UNIT2|M2|N2|O2|P2|Q2|R2|
|A3|7:02|C3|D3|555666|F3|G3|H3|I3|ADDR3|CITY3|UNIT3|M3|N3|O3|P3|Q3|R3|

but need to look like this…

|A0|B000|C0|D0|E00000|F0|G0|H0|I0|J0000|K0000|L0000|M0000|N0000|O0000|P0000|Q0000|R0000|
|A1|7:00|C1|D1|555666|F1|G1|H1|I1|ADDR1|CITY1|UNIT1|ADDR2|CITY2|UNIT2|ADDR3|CITY3|UNIT3|
|A2|7:01|C2|D2|555666|F2|G2|H2|I2|-----|-----|-----|-----|-----|-----|-----|-----|-----|
|A3|7:02|C3|D3|555666|F3|G3|H3|I3|-----|-----|-----|-----|-----|-----|-----|-----|-----|

once those addresses are moved to the appropriate row i’ll need to sort the whole thing by COLUMN J and delete the rows that have no data in COLUMN J.

thanks to anybody who is willing to take a stab at this.

#2

You’ve got the logic worked out pretty good, so you just need to break down your task into smaller steps and then work each step. For example…

  1. do you know how to get all of the cells in a particular column of a particular work sheet?
  2. can you use a repeat loop on one cell at a time so you can compare it with another?
  3. do you know how to get and set the contents of a cell?
  4. do you know the if/then statement for comparing 2 things?

If you can do those things then you should be able to perform your task. I’m sure the answer to each of those steps is already in the forums so you just need to do a little searching and put the whole thing together.

If you have a specific question about a particular step then just ask. I don’t think anyone will write the whole thing… but you can get help for one small step of the process.

#3

thanks for the response regulus. i’ve been looking online for things and have found most of them, but there are certain things i’m just missing.

i spent some time on this problem this evening and have come up with an “almost” solution for the brunt of it. It’s not the most concise script, but it functions as expected.

i can get the data to move to where it’s supposed to be, but i can’t do it based on the one or two minute time difference prerequisite i have for column B (which contains a time displayed as 24 hour… hh:mm)

also, at the end i can sort based on the proper column (J), but can’t figure out how to delete the rows whose “J” cell is empty.

tell application "Microsoft Excel"
	activate
	set cell_No to 2
	set cell_E_a to "not empty"
	set cell_E_b to "not empty"
	set cell_E_c to "not empty"
	set cell_B_a to "not empty"
	set cell_B_b to "not empty"
	set cell_B_c to "not empty"
	
	repeat until cell_E_a is ""
		set cell_E_a to value of cell ("E" & cell_No)
		set cell_E_b to value of cell ("E" & cell_No + 1)
		set cell_B_a to value of cell ("B" & cell_No)
		set cell_B_b to value of cell ("B" & cell_No + 1)
		if cell_E_a is cell_E_b then
			set new_M to value of cell ("J" & cell_No + 1)
			set new_N to value of cell ("K" & cell_No + 1)
			set new_O to value of cell ("L" & cell_No + 1)
			set value of cell ("M" & cell_No) to new_M
			set value of cell ("N" & cell_No) to new_N
			set value of cell ("O" & cell_No) to new_O
			clear contents of cell ("J" & cell_No + 1)
			clear contents of cell ("K" & cell_No + 1)
			clear contents of cell ("L" & cell_No + 1)
		end if
		set cell_E_a to value of cell ("E" & cell_No)
		set cell_E_c to value of cell ("E" & cell_No + 2)
		set cell_B_a to value of cell ("B" & cell_No)
		set cell_B_c to value of cell ("B" & cell_No + 2)
		if cell_E_a is cell_E_c then
			set new_P to value of cell ("J" & cell_No + 2)
			set new_Q to value of cell ("K" & cell_No + 2)
			set new_R to value of cell ("L" & cell_No + 2)
			set value of cell ("P" & cell_No) to (new_P)
			set value of cell ("Q" & cell_No) to (new_Q)
			set value of cell ("R" & cell_No) to (new_R)
			clear contents of cell ("J" & cell_No + 2)
			clear contents of cell ("K" & cell_No + 2)
			clear contents of cell ("L" & cell_No + 2)
		end if
		set cell_No to cell_No + 1
	end repeat
	sort range "J1" order1 sort ascending key1 column ¬
		"J:J" header header yes without match case
end tell

i tried to use this bit for the time problem but it didn’t work

		if cell_E_a is cell_E_b then
			if cell_B_b is (cell_B_a + 1) then
				set new_M to value of cell ("J" & cell_No + 1)
				set new_N to value of cell ("K" & cell_No + 1)
				set new_O to value of cell ("L" & cell_No + 1)
				set value of cell ("M" & cell_No) to new_M
				set value of cell ("N" & cell_No) to new_N
				set value of cell ("O" & cell_No) to new_O
				clear contents of cell ("J" & cell_No + 1)
				clear contents of cell ("K" & cell_No + 1)
				clear contents of cell ("L" & cell_No + 1)
			end if
		end if
#4

Here’s how you can compare the times. First we change your format for the times into seconds and then make the comparison…

-- this is the time you get from the cells you want to compare
set firstTime to "02:30"
set secondTime to "02:33"

-- first we change the times into seconds
set firstTime_asSeconds to changeTime_toSeconds(firstTime)
set secondTime_asSeconds to changeTime_toSeconds(secondTime)

-- then we make the comparison and do something
set timeDiff to secondTime_asSeconds - firstTime_asSeconds
if timeDiff is greater than 120 then
	return "time difference is more than 2 minutes"
else if timeDiff is greater than or equal to 60 then
	return "time difference is between 1 minutes and 2 minutes"
else
	return "time difference is less than 1 minute"
end if

on changeTime_toSeconds(theTime)
	set AppleScript's text item delimiters to ":"
	set a to text items of theTime
	set AppleScript's text item delimiters to ""
	set hoursAsSeconds to (item 1 of a) * 60 * 60
	set minutesAsSeconds to (item 2 of a) * 60
	return (hoursAsSeconds + minutesAsSeconds)
end changeTime_toSeconds
#5

Here’s how you can loop through your data and delete a rows with an empty “J” cell.

-- the idea here is to first find the rows where your data begins and ends so we know what rows contain your data. Then we loop through those rows looking for an empty cell in column J. If we find one we delete that row and start the whole process over. We keep doing this until we don't find an empty J cell, because then we know we're finished.
-- in this code I assume column A is a column where you would have no empty cells, so we can use that column for the first part of the task to identify where your data begins and ends

set foundOne to true
tell application "Microsoft Excel"
	repeat until foundOne is false
		set foundOne to false
		-- first we identify the rows where your data starts and ends
		set targetColumn to column 1 of the active sheet
		set firstDataCell to cell "A1" in the active sheet
		set theResult to find targetColumn what "" after firstDataCell look in values look at whole search direction search next
		set rowNumberWhereDataEnds to (first row index of theResult) - 1
		
		-- now we can loop through the rows we identified looking for empty cells in column J
		repeat with i from 1 to rowNumberWhereDataEnds
			set thisCell to "J" & i
			if value of cell thisCell is "" then
				delete row i
				set foundOne to true
				exit repeat
			end if
		end repeat
	end repeat
end tell
#6

Here’s one more script which might help. You can use this to open your file and select the proper worksheet.

-- here's how you can open a file and select the proper worksheet
set excelFileName to "testSpreadsheet.xlsx"
set excelFilePath to (path to desktop folder as text) & excelFileName
set worksheetName to "testSheet"

tell application "Microsoft Excel"
	-- check if the file is already open and if not then open it
	set currentWorkbooks to name of workbooks
	if currentWorkbooks contains excelFileName then
		activate object workbook excelFileName
	else
		open excelFilePath
	end if
	
	-- select the worksheet
	select sheet worksheetName
end tell
#7

thanks for the help, regulus. the code to delete rows works well, but the time comparison doesn’t work inside excel… “Applescript Error Microsoft Excel got an error: Can’t continue changeTime_toSeconds.” with the “changeTime_toSeconds(firstTime)” code highlighted.

i’m sure it has to do with the way that excel stores the data in each field.

here are some examples: given a cell with a displayed value of 7:15

tell application "Microsoft Excel"
	activate
	set cell_No to 2
	set cell_B_a to value of cell ("B" & cell_No)
	return cell_B_a
end tell

returns “0.302083333333” (7:16 gets “0.302777777778”)
while

tell application "Microsoft Excel"
	activate
	set cell_No to 2
	set cell_B_a to value of cell ("B" & cell_No) as integer
	return cell_B_a
end tell

returns “0”
while…

tell application "Microsoft Excel"
	activate
	set cell_No to 2
	set cell_B_a to value of cell ("B" & cell_No) as time
	return cell_B_a
end tell

errors out with “expected class name but found property.”

and

tell application "Microsoft Excel"
	activate
	set cell_No to 2
	set cell_B_a to value of cell ("B" & cell_No) as date
	return cell_B_a
end tell

errors out with “can’t make 0.302083333333” into type date."

#8

This should do what you want.

tell application "Microsoft Excel"
	set lastRow 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)
	
end tell
repeat with rownum from lastRow to 2 by -1
	if TimeInterVal(rownum - 1) = 1 then
		if 1 < rownum then
			tell application "Microsoft Excel"
				set value of (range "m1:am1" of row (rownum - 1)) to (get value of (range "j1:aj1" of row rownum))
				delete range (row rownum of active sheet) shift shift up
			end tell
		end if
		
	end if
end repeat

on TimeInterVal(rowNumber)
	try
		tell application "Microsoft Excel"
			if (get value of cell 5 of row rowNumber) = (get value of cell 5 of row (rowNumber + 1)) then
				set startDay to get value of (cell 1 of row rowNumber) of active sheet
				set startTime to (get value of (cell 2 of row rowNumber) of active sheet) * 24 * minutes * minutes
				set endDay to get value of (cell 1 of row (rowNumber + 1)) of active sheet
				set endTime to (get value of (cell 2 of row (rowNumber + 1)) of active sheet) * 24 * minutes * minutes
				return ((endDay + endTime) - (startDay + startTime)) / minutes
			else
				return -1
			end if
		end tell
		
	on error
		return -1
	end try
end TimeInterVall
#9

wow. that’s a helluva lot more efficient than what i came up with and it works very very well.

there was a small problem with data being shifted to the right, but that was my fault because i didn’t mention that i have data in COLUMN V that needs to stay where it is so the range being moved was bigger than i had room for.

the solution was as simple as changing…

set value of (range "m1:am1" of row (rownum - 1)) to (get value of (range "j1:aj1" of row rownum))

to

set value of (range "m1:u1" of row (rownum - 1)) to (get value of (range "j1:r1" of row rownum))

this has the benefit of allowing up to 4 stops vs. the 3 i was anticipating but takes away some of the benefit of scalability that your original range provided.

thanks to everybody for the help.

i learned a lot looking at scripts provided.

-david

#10

I’m glad to help. I never mind helping someone who is also working the problem themself.

I thought about the script where we delete empty “J” rows. We had a repeat loop inside a repeat loop. I figured if we could repeat through the inside loop starting at the last row and moving to the first row we could eliminate the outside repeat loop. As such here’s an updated script for that. It should be much more efficient.

tell application "Microsoft Excel"
	-- first we identify the rows where your data starts and ends
	-- this was taken from mikerickson script above... it seems more efficient that what I previously used
	set rowNumberWhereDataEnds 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)
	
	-- now we can loop through the rows we identified looking for empty cells in column J
	-- we loop from the last row to the first so we don't mess up the row numbers we're looping over when deleting rows, thus eliminating a repeat loop
	repeat with i from rowNumberWhereDataEnds to 1 by -1
		if value of cell ("J" & (i as string)) is "" then delete row i
	end repeat
end tell
#11

I’m glad the trip consolidation routine worked for you.

This will delete all rows where the cell in column J is blank, without looping

tell application "Microsoft Excel"
	try
		delete range (entire row of (special cells of range "J:J" type cell type blanks)) shift shift up
	end try
end tell
#12

excellent.

i was a bit worried about the speed of the original routine to delete rows because the file being processed generally has between 250-350 lines. Mike’s script deletes the offending rows as it moves the data over, but occasionally the file that this is all sourced from throws in an extra carriage return which adds a few extra characters at the bottom which chokes the database this is all imported into… the new delete elements address that very nicely.

i think that this is puts the final touches on this project… 460 lines in applescript, 7 applications, 2 operating systems (OS X & windows 2000 (system events running parallels), remote printing…

a little more testing to make sure it’ll run unattended and that’ll be that.

thanks SOOOOO much to the two of you. i couldn’t have done it on my own.

-david