The script below is very simple I am trying in the example to see if items in ColorNew exists in ColorExists.
I pick up the two lists from sheets 8 & 9 and at present rebuild the ColorExists list. In practice both those list will be large so I am trying to avoid the loop that presently rebuilds the existing list. As you can see I have left in (but grayed out) my attempt to coerce the list I am checking against into a list.
tell application "Microsoft Excel"
set ColorExists to {}
set RowsNew to (first row index of (last cell of used range of (worksheet "sheet8"))) --Blue,Black,Red,Yellow,Pink
set ColorNew to string value of range ("A3:A" & RowsNew) of (worksheet "sheet8")
log ColorNew
set RowsExist to (first row index of (last cell of used range of (name of worksheet "sheet9"))) --Blue,Black,Red,Orange,Purple
set ColorExists to (value of range ("A3:A" & RowsExist) of worksheet "sheet9")
log ColorExists
--set end of ColorExists to (value of range ("A3:A" & RowsExist) of worksheet "Sheet9") as list
repeat with CntSym from 1 to count of ColorExists
set end of ColorExists to item 1 of item CntSym of ColorExists
end repeat
repeat with cnt from (count of ColorNew) to 1 by -1
if ColorExists contains (item 1 of item cnt of ColorNew) then
display dialog "it does" & " " & (item 1 of item cnt of ColorNew)
else
display dialog "it does not" & " " & (item 1 of item cnt of ColorNew)
end if
end repeat
end tell
Browser: Safari 605.1.15
Operating System: macOS 12
This will put “it does ____” or “it does not ____” for each color into a results list. I’ll leave it as an exercise for the reader to use display dialog instead. (Though why you would want to do that if, as you say, “both those list will be large”, I have no idea.)
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
set TRUE_MESSAGE to "it does "
set FALSE_MESSAGE to "it does not "
tell application "Microsoft Excel"
set rowsNew to column 1 of current region of range "$A$3" of worksheet "sheet8"
set rowsExistAddress to get address of column 1 of current region of range "$A$3" of worksheet "sheet9" with external
set newColors to string value of rowsNew
set results to string value of rowsNew -- so we have a list of the same size as newColors
repeat with i from 1 to count of newColors
set searchTerm to (item i of newColors) as text
set trueResult to "\"" & TRUE_MESSAGE & searchTerm & "\""
set falseResult to "\"" & FALSE_MESSAGE & searchTerm & "\""
--"IF(COUNTIF(range, searchTerm)>0, true, false)"
set item i of results to evaluate name "=IF(COUNTIF(" & rowsExistAddress & ",\"" & searchTerm & "\")>0," & trueResult & "," & falseResult & ")"
end repeat
results
end tell
Thanks for your reply. I did not explain my issue well, items on “sheet8” not on “sheet9” will be transferred to that sheet. Running your script did not show the missing items.
The issue is that data I pull from Sheet9. My original solution (and still I think the only way) was to build a list of the data and I was trying to save a little time by avoiding that step.
In the following script I have hard keyed the data I am checking against and it works.
I assume there is no other way but to loop through the existing data to build a new list.
set Results to {}
tell application "Microsoft Excel"
set ColorExists to {"Blue", "Black", "Red", "Orange", "Purple"}
set ColorNew to value of range ("A3:A7") of worksheet "Sheet8"
repeat with cnt from (count of ColorNew) to 1 by -1
if ColorExists contains (item 1 of item cnt of ColorNew) then
set end of Results to "it does" & " " & (item 1 of item cnt of ColorNew)
else
set end of Results to "it does not" & " " & (item 1 of item cnt of ColorNew)
end if
end repeat
end tell
log Results
Here’s the result I get from your code:
{"it does not Pink", "it does not Yellow", "it does Red", "it does Black", "it does Blue"}
Here’s the result I get from my code:
{"it does Blue", "it does Black", "it does Red", "it does not Yellow", "it does not Pink"}
Apart from the order, what is different that doesn’t work for you?
Again my apologies the active sheet was sheet9 if I made the active sheet sheet8 I got the correct result.
However I still have not resolved my issue as I need to process any item that is on sheet8 but not sheet9. Obviously I do not need the “it does” & “it does not” just the way to process those not on sheet9. I cannot figure out how to parse your evaluation line.
I apologise for taking your time and totally understand if you do not want to waste any more of it.
thanks
Peter
Oh and the reason our lists where the other way around is that in actual use I delete the row that will be transferred to sheet9.
Sorry I was not thinking I have worked it out , thank you again. I managed to confuse myself with the terms I was using for testing. The "with external " is something I have never used. I looked in the AppleScript and Excel dictonary but failed to look under get address.
You didn’t mention in your original post that you were wanting to delete rows. You indicated you wanted to build a list of “it does” and “it does not”. In the future, please state what you actually want to do, as it can affect how people answer your questions.
Anyway, here’s a script that should do what you want:
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
(*
Utility function to take a nested list of text items, like:
{{"Blue"},{"Black"},{"Purple"}}
and flatten it into a single-level list of text items, like:
{"Blue","Black","Purple"}
REMEMBER: Excel ranges are returned as nested AppleScript lists!
*)
to flatten:aList
set outputList to {}
repeat with anItem in aList
set end of outputList to anItem as text
end repeat
return outputList
end flatten:
on run
-- the row where our ranges begin
set startRow to 3
-- get the data from Excel
tell application "Microsoft Excel"
-- keep a reference to the sheet to make things easier later
set newColorsSheet to worksheet "sheet8"
tell newColorsSheet
-- where does our list of colors start?
set rngStart to range ("$A$" & startRow)
-- now go to the end, just like using Cmd+down arrow
set rngEnd to get end rngStart direction toward the bottom
-- make our range using the start and end cell addresses
set newColorsRange to range ({(get address rngStart), ":", (get address rngEnd)} as text)
-- and get a list of the colors inn the range
-- this is in the format {{"Blue"},{"Black"},{"Purple"}}
set newColors to string value of newColorsRange
end tell
-- keep a reference to the sheet to make things easier later
set existingColorsSheet to worksheet "sheet9"
tell existingColorsSheet
-- where does our list of colors start?
set rngStart to range ("$A$" & startRow)
-- now go to the end, just like using Cmd+down arrow
set rngEnd to get end rngStart direction toward the bottom
-- make our range using the start and end cell addresses
set existingColorsRange to range ({(get address rngStart), ":", (get address rngEnd)} as text)
-- and get a list of the colors inn the range
-- this is in the format {{"Blue"},{"Black"},{"Purple"}}
set existingColors to string value of existingColorsRange
end tell
end tell
--and we're done with Excel for now!
-- set up a list to hold the rows we want to remove when we're done
set rowsToDelete to {}
-- flatten the list of colors to make it easier to search
-- this will be in the format {"Blue","Black","Purple"}
set colorsForTest to my flatten:existingColors
-- now, loop through the list of newColors
repeat with idx from 1 to count of newColors
-- get the item as "Blue" instead of {"Blue"}
set aColor to (item idx of newColors) as text
-- do we already have this color in the existing list?
if colorsForTest does not contain aColor then
-- no? okay then, add it to the list
set end of existingColors to {aColor}
-- we want to delete this row
-- we get the index of the current item and add it to the row we started from
-- remembering to subtract 1 to make the math work
set deleteRow to (idx - 1) + startRow
-- now add this range address to the list
-- in the format "$1:$1" to indicate to Excel that we want the entire row
set end of rowsToDelete to "$" & deleteRow & ":$" & deleteRow
end if
end repeat
-- check if we actually found some new colors
-- we know whether this is the case if we have some rows to delete
if rowsToDelete is {} then
-- nope! so we can quit in good conscience
error number -128
end if
-- our list of rows to delete looks like this: {"$1:$1", "$5:$5"}
-- and we need it to be a string like this: "$1:$1,$5:$5"
set {oldDelims, AppleScript's text item delimiters} to {AppleScript's text item delimiters, ","}
set rowsToDelete to rowsToDelete as text
set AppleScript's text item delimiters to oldDelims
-- now we can jump back into Excel for finishing up
tell application "Microsoft Excel"
-- remember how we saved a reference to the sheet for later? smart!
tell existingColorsSheet
-- resize our existingColorsRange to accomodate all the new colors we're going to add
set newExistingColorsRange to get resize existingColorsRange row size (count of existingColors)
-- set the value of the expanded range
set value of newExistingColorsRange to existingColors
end tell
-- finally, get rid of the rows from the newColorsSheet that were added to existingColorsSheet
tell newColorsSheet
-- using a range like "$1:$1,$5:$5" lets us get rid of entire rows all at once
-- avoiding having to loop through the rows backwards
delete range range rowsToDelete
end tell
end tell
end run
Given these starting lists:
It ends up like this:
