Not real familiar to scripting excel and need a little help.
I have an excel file with 3 columns (column 1 = a number 1-50, could have 4 rows with the number 1, 5 rows with 2 and so on / column 2 = a sku# / column 3 = a description)
I am looking to see if there is a way that you can find the first #1 in column 1 then find the next #1 from column 1 and copy that row to the end of the row of the first #1 found.
EXAMPLE:
item sku description
1 12345 my first item
1 98765 my second item
2 45678 my third item
TO:
item sku description sku2 description 2
1 12345 my first item 98765 my second item
2 45678 my third item
I hope that you can understand this, if not I will be happy to try to help explain further. I am trying to take this and get it in a format to do a data merge in InDesign.
hi
If I understand you correctly,something like this might work.
tell application "Microsoft Excel"
activate
repeat
if get value of range "A3" is not 1 then exit repeat
set copyRange1 to range "b1:c1"
set copyRange2 to range "b3:c3"
set TargetRange1 to row 1 of the active sheet
set TargetRange2 to row 2 of the active sheet
set AfterCell1 to cell "A1" in the active sheet
set AfterCell2 to cell "A2" in the active sheet
set FoundRange1 to find TargetRange1 what "" after AfterCell1 look in values look at whole search direction search next
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange1 destination FoundRange1
copy range copyRange2 destination FoundRange2
delete range range "a3:c3" shift shift up
end repeat
end tell
Thanks for the help. I was able to take part of what you did and come up with a script that will do this for all the needed numbers that I have.
Buty alas this is real time consuming, does anyone have any ideas on how I can streamline this script to run faster?
thanks for all the help.
on run
set chosenFiles to choose file with multiple selections allowed
open chosenFiles
end run
on open theDropped --this makes it so you can drop files onto the icon of the app
set this_folder to (path to desktop folder)
set savedFolder to (this_folder & "configs") as string
tell application "Microsoft Excel"
open theDropped
end tell
tell application "Finder"
set savedFile to savedFolder & ":W_Runway_Looks.xlxs"
end tell
------ puts the rows to be in columns
tell application "Microsoft Excel"
repeat 7 times
set copyRange1 to range "b1:c1"
set TargetRange1 to row 1 of the active sheet
set AfterCell1 to cell "A1" in the active sheet
set FoundRange1 to find TargetRange1 what "" after AfterCell1 look in values look at whole search direction search next
copy range copyRange1 destination FoundRange1
end repeat
set endOfRow to first row index of (get end (cell 1 of row (count rows)) direction toward the top)
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 1 then
set copyRange2 to range "b3:c3"
set TargetRange2 to row 3 of the active sheet
set AfterCell2 to cell "A3" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a3:c3" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 2 then
set copyRange2 to range "b4:c4"
set TargetRange2 to row 4 of the active sheet
set AfterCell2 to cell "A4" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a4:c4" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 3 then
set copyRange2 to range "b5:c5"
set TargetRange2 to row 5 of the active sheet
set AfterCell2 to cell "A5" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a5:c5" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 4 then
set copyRange2 to range "b6:c6"
set TargetRange2 to row 6 of the active sheet
set AfterCell2 to cell "A6" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a6:c6" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 5 then
set copyRange2 to range "b7:c7"
set TargetRange2 to row 7 of the active sheet
set AfterCell2 to cell "A7" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a7:c7" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 6 then
set copyRange2 to range "b8:c8"
set TargetRange2 to row 8 of the active sheet
set AfterCell2 to cell "A8" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a8:c8" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 7 then
set copyRange2 to range "b9:c9"
set TargetRange2 to row 9 of the active sheet
set AfterCell2 to cell "A9" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a9:c9" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 8 then
set copyRange2 to range "b10:c10"
set TargetRange2 to row 10 of the active sheet
set AfterCell2 to cell "A10" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a10:c10" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 9 then
set copyRange2 to range "b11:c11"
set TargetRange2 to row 11 of the active sheet
set AfterCell2 to cell "A11" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a11:c11" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 10 then
set copyRange2 to range "b12:c12"
set TargetRange2 to row 12 of the active sheet
set AfterCell2 to cell "A12" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a12:c12" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 11 then
set copyRange2 to range "b13:c13"
set TargetRange2 to row 13 of the active sheet
set AfterCell2 to cell "A13" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a13:c13" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 12 then
set copyRange2 to range "b14:c14"
set TargetRange2 to row 14 of the active sheet
set AfterCell2 to cell "A14" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a14:c14" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 13 then
set copyRange2 to range "b15:c15"
set TargetRange2 to row 15 of the active sheet
set AfterCell2 to cell "A15" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a15:c15" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 14 then
set copyRange2 to range "b16:c16"
set TargetRange2 to row 16 of the active sheet
set AfterCell2 to cell "A16" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a16:c16" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 15 then
set copyRange2 to range "b17:c17"
set TargetRange2 to row 17 of the active sheet
set AfterCell2 to cell "A17" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a17:c17" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 16 then
set copyRange2 to range "b18:c18"
set TargetRange2 to row 18 of the active sheet
set AfterCell2 to cell "A18" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a18:c18" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 17 then
set copyRange2 to range "b19:c19"
set TargetRange2 to row 19 of the active sheet
set AfterCell2 to cell "A19" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a19:c19" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 18 then
set copyRange2 to range "b20:c20"
set TargetRange2 to row 20 of the active sheet
set AfterCell2 to cell "A20" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a20:c20" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 19 then
set copyRange2 to range "b21:c21"
set TargetRange2 to row 21 of the active sheet
set AfterCell2 to cell "A21" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a21:c21" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 20 then
set copyRange2 to range "b22:c22"
set TargetRange2 to row 22 of the active sheet
set AfterCell2 to cell "A22" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a22:c22" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 21 then
set copyRange2 to range "b23:c23"
set TargetRange2 to row 23 of the active sheet
set AfterCell2 to cell "A23" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a23:c23" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 22 then
set copyRange2 to range "b24:c24"
set TargetRange2 to row 24 of the active sheet
set AfterCell2 to cell "A24" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a24:c24" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 23 then
set copyRange2 to range "b25:c25"
set TargetRange2 to row 25 of the active sheet
set AfterCell2 to cell "A25" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a25:c25" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 24 then
set copyRange2 to range "b26:c26"
set TargetRange2 to row 26 of the active sheet
set AfterCell2 to cell "A26" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a26:c26" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 25 then
set copyRange2 to range "b27:c27"
set TargetRange2 to row 27 of the active sheet
set AfterCell2 to cell "A27" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a27:c27" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 26 then
set copyRange2 to range "b28:c28"
set TargetRange2 to row 28 of the active sheet
set AfterCell2 to cell "A28" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a28:c28" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 27 then
set copyRange2 to range "b29:c29"
set TargetRange2 to row 29 of the active sheet
set AfterCell2 to cell "A29" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a29:c29" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 28 then
set copyRange2 to range "b30:c30"
set TargetRange2 to row 30 of the active sheet
set AfterCell2 to cell "A30" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a30:c30" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 29 then
set copyRange2 to range "b31:c31"
set TargetRange2 to row 31 of the active sheet
set AfterCell2 to cell "A31" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a31:c31" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 30 then
set copyRange2 to range "b32:c32"
set TargetRange2 to row 32 of the active sheet
set AfterCell2 to cell "A32" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a32:c32" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 31 then
set copyRange2 to range "b33:c33"
set TargetRange2 to row 33 of the active sheet
set AfterCell2 to cell "A33" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a33:c33" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 32 then
set copyRange2 to range "b34:c34"
set TargetRange2 to row 34 of the active sheet
set AfterCell2 to cell "A34" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a34:c34" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 33 then
set copyRange2 to range "b35:c35"
set TargetRange2 to row 35 of the active sheet
set AfterCell2 to cell "A35" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a35:c35" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 34 then
set copyRange2 to range "b36:c36"
set TargetRange2 to row 36 of the active sheet
set AfterCell2 to cell "A36" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a36:c36" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 35 then
set copyRange2 to range "b37:c37"
set TargetRange2 to row 37 of the active sheet
set AfterCell2 to cell "A37" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a37:c37" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 36 then
set copyRange2 to range "b38:c38"
set TargetRange2 to row 38 of the active sheet
set AfterCell2 to cell "A38" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a38:c38" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 37 then
set copyRange2 to range "b39:c39"
set TargetRange2 to row 39 of the active sheet
set AfterCell2 to cell "A39" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a39:c39" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 38 then
set copyRange2 to range "b40:c40"
set TargetRange2 to row 40 of the active sheet
set AfterCell2 to cell "A40" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a40:c40" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 39 then
set copyRange2 to range "b41:c41"
set TargetRange2 to row 41 of the active sheet
set AfterCell2 to cell "A41" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a41:c41" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 40 then
set copyRange2 to range "b42:c42"
set TargetRange2 to row 42 of the active sheet
set AfterCell2 to cell "A42" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a42:c42" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 41 then
set copyRange2 to range "b43:c43"
set TargetRange2 to row 43 of the active sheet
set AfterCell2 to cell "A43" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a43:c43" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 42 then
set copyRange2 to range "b44:c44"
set TargetRange2 to row 44 of the active sheet
set AfterCell2 to cell "A44" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a44:c44" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 43 then
set copyRange2 to range "b45:c45"
set TargetRange2 to row 45 of the active sheet
set AfterCell2 to cell "A45" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a45:c45" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 44 then
set copyRange2 to range "b46:c46"
set TargetRange2 to row 46 of the active sheet
set AfterCell2 to cell "A46" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a46:c46" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 45 then
set copyRange2 to range "b47:c47"
set TargetRange2 to row 47 of the active sheet
set AfterCell2 to cell "A47" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a47:c47" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 46 then
set copyRange2 to range "b48:c48"
set TargetRange2 to row 48 of the active sheet
set AfterCell2 to cell "A48" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a48:c48" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 47 then
set copyRange2 to range "b49:c49"
set TargetRange2 to row 49 of the active sheet
set AfterCell2 to cell "A49" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a49:c49" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 48 then
set copyRange2 to range "b50:c50"
set TargetRange2 to row 50 of the active sheet
set AfterCell2 to cell "A50" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a50:c50" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 49 then
set copyRange2 to range "b51:c51"
set TargetRange2 to row 51 of the active sheet
set AfterCell2 to cell "A51" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a51:c51" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 50 then
set copyRange2 to range "b52:c52"
set TargetRange2 to row 52 of the active sheet
set AfterCell2 to cell "A52" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a52:c52" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 51 then
set copyRange2 to range "b53:c53"
set TargetRange2 to row 53 of the active sheet
set AfterCell2 to cell "A53" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a53:c53" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 52 then
set copyRange2 to range "b54:c54"
set TargetRange2 to row 54 of the active sheet
set AfterCell2 to cell "A54" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a54:c54" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 53 then
set copyRange2 to range "b55:c55"
set TargetRange2 to row 55 of the active sheet
set AfterCell2 to cell "A55" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a55:c55" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 54 then
set copyRange2 to range "b56:c56"
set TargetRange2 to row 56 of the active sheet
set AfterCell2 to cell "A56" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a56:c56" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 55 then
set copyRange2 to range "b57:c57"
set TargetRange2 to row 57 of the active sheet
set AfterCell2 to cell "A57" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a57:c57" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 56 then
set copyRange2 to range "b58:c58"
set TargetRange2 to row 58 of the active sheet
set AfterCell2 to cell "A58" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a58:c58" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 57 then
set copyRange2 to range "b59:c59"
set TargetRange2 to row 59 of the active sheet
set AfterCell2 to cell "A59" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a59:c59" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 58 then
set copyRange2 to range "b60:c60"
set TargetRange2 to row 60 of the active sheet
set AfterCell2 to cell "A60" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a60:c60" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 59 then
set copyRange2 to range "b61:c61"
set TargetRange2 to row 61 of the active sheet
set AfterCell2 to cell "A61" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a61:c61" shift shift up
end if
end repeat
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 60 then
set copyRange2 to range "b62:c62"
set TargetRange2 to row 62 of the active sheet
set AfterCell2 to cell "A62" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a62:c62" shift shift up
end if
end repeat
---- deletes the 2 columns that is not needed now
delete range range "B:C"
tell application "Microsoft Excel"
tell active workbook
save workbook as filename savedFile with overwrite
end tell
end tell
end tell
---- opens another workbook to add the look numbers and image names
tell application "Finder"
set this_folder to (path to desktop folder)
set myFile to (((path to me) as text) & "looks.xlsx") as string
end tell
tell application "Microsoft Excel"
activate
open myFile
copy range (range "A1:A100" of sheet 1)
activate
activate next active window
tell sheet 1
paste worksheet destination range "A3:A103"
end tell
activate next active window
copy range (range "B1:B100" of sheet 1)
activate next active window
tell sheet 1
paste worksheet destination range "P3:P103"
end tell
insert into (character 0 of range "Q1") string "@Image"
end tell
end open
I put in a sort,brings all the 1’s to the top–this is about 3 times as fast
tell application "Microsoft Excel"
activate
sort range "a1" of active sheet key1 (range "a1" of active sheet)
repeat
if get value of range "A3" is not 1 then exit repeat
set copyRange2 to range "b3:c3"
set TargetRange2 to row 2 of the active sheet
set AfterCell2 to cell "A2" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a3:c3" shift shift up
end repeat
repeat
if get value of range "A4" is not 2 then exit repeat
set copyRange2 to range "b4:c4"
set TargetRange2 to row 3 of the active sheet
set AfterCell2 to cell "A3" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a4:c4" shift shift up
end repeat
repeat
if get value of range "A5" is not 3 then exit repeat
set copyRange2 to range "b5:c5"
set TargetRange2 to row 4 of the active sheet
set AfterCell2 to cell "A4" in the active sheet
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a5:c5" shift shift up
end repeat
end tell
I ran part of your script,If you want to copy to row2 your “A3” should read “A2”
repeat with i from endOfRow to 2 by -1
if value of (cell 1 of row i) = 1 then
set copyRange2 to range "b3:c3"
set TargetRange2 to row 3 of the active sheet
set AfterCell2 to cell "A3" in the active sheet--"A2"
set FoundRange2 to find TargetRange2 what "" after AfterCell2 look in values look at whole search direction search next
copy range copyRange2 destination FoundRange2
delete range range "a3:c3" shift shift up
end if
end repeat
you might be able to use lists to speed it up more
bills
basically what this is is a list of clothes on a model. So starting on row 3 it lists the items for the 1st image. I will have anywhere from 1-7 articles of clothing on the model. So…
Row 1 is the top for image 1
Row 2 is the pants for image 1
Row 3 is the necklace for image 1
Row 4 is the scarf for image 1
All the items are already in order and I go thru and put in a 1 in each row for image 1, so if there is 4 items of clothing for image 1, I add a 1 in column 1 for the first 4 rows. But I need these 4 rows to be in one row. So that all items of clothing for image 1 is on 1 row. And all images for Image 2 to be on row 2 and so on. We can have anywhere from 40 - 80 images that I need to do this for. I thought I could accomplish faster with an applescript.
I am still trying to learn more about working with lists, so i have been struggling with this and lists.
I hope this explains a little more of what I am trying to accomplish