Well like everyone else, I am fairly new to this. I have an excel file that I am pulling information out of and putting in a new workbook. this is what I have it doing now to get everything in the order that I need it. I have the script to look in column c and find the size, copy that row , past that row to the bottom and then delete the row from the top (so it no longer is there, don’t need duplicates!)
I have all of that working but I also want the script to find how many rows there are per size and then if the row are less than twenty add a row with an asterisk in between the sizes so that it does equal 20.
there are 5 columns to the excel spreadsheet (product #, group, size, description and image name) I have tried to just sort the columns, but it does not put everything in the order that i need it.
here is the code that I have for sorting:
tell application “Microsoft Excel”
tell sheet 1 of workbook 1
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 3 of row i) = " " then
delete entire row of (cell 1 of row i)
end if
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 3 of row i) = “00” then
copy range entire row of (cell 1 of row i)
insert into range row (endOfRow + 2) of sheet “Sheet1”
delete range entire row of (cell 1 of row i)
end if
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 3 of row i) = “02” then
copy range entire row of (cell 1 of row i)
insert into range row (endOfRow + 4) of sheet “Sheet1”
delete range entire row of (cell 1 of row i)
end if
end repeat
end tell
end tell
and here is what I have been working on to do both:
tell application “Microsoft Excel”
set redcount to 0
set bluecount to 0
set numCells to first row index of (get end (cell 1 of row (count rows)) direction toward the top)
repeat with i from 1 to numCells
if the value of (cell 3 of row i) = “04” then
set red to entire row of (cell 3 of row i)
copy range entire row of (cell 3 of row i)
insert into range row (numCells + 4) of sheet “Sheet1”
set redcount to redcount + 1
end if
end repeat
set numCells to first row index of (get end (cell 1 of row (count rows)) direction toward the top)
repeat with i from 1 to numCells
if the value of (cell 3 of row i) = “06” then
copy range entire row of (cell 3 of row i)
insert into range row (numCells + 2) of sheet “Sheet1”
set bluecount to bluecount + 1
end if
end repeat
set numCells to first row index of (get end (cell 1 of row (count rows)) direction toward the top)
set firstRowRed to first row index of red
end tell
return redcount & bluecount
any Help would be great!!! :o
Thanks,
I’m not sure what your ultimate goal is?
It sounds like you have data on a worksheet. In Column C you have Size.
You want the data sorted by size (putting all of each size together)
And if for all sizes that have less than 20 rows of data, you want dummy rows inserted until there are 20 rows associated with that size.
Is that the situation?
If so, do you have a header row?
Also, about how many rows?
yes, that pretty much sums it up. But, I do need the sizes in a specific order.
yes there is a header row and there is about 319 rows of information. But, the amount of rows change all the time.
I think this might do what you want. This sorts ascending, but if you need a custom sort it need be applied only to the sort in the final tell block.
set minCount to 20
set fillString to "***"
tell application "Microsoft Excel"
set dataRange to get resize (range "A1:E1") row size (first row index of (get end (cell -1 of column 1 of active sheet) direction toward the top)) column size 5
sort dataRange key1 (column 3 of dataRange) header header yes
set sizeList to get value of (get resize (get offset (column 3 of dataRange) row offset 1) row size (count of rows of dataRange) - 1)
end tell
set uniqueSizes to {}
repeat with oneSize in sizeList
set aSize to item 1 of oneSize
if not ({aSize} is in uniqueSizes) then
copy aSize to end of uniqueSizes
end if
end repeat
if true then
repeat with oneSize in uniqueSizes
if oneSize ≠"" then
tell application "Microsoft Excel"
set oneChunk to get resize (get offset (get end (cell -1 of column 3) direction toward the top) row offset 1) row size minCount
tell entire row of oneChunk
set value of column 1 to fillString
set value of column 3 to oneSize
end tell
end tell
end if
end repeat
end if
tell application "Microsoft Excel"
tell used range of worksheet object of dataRange
set HelperColumn to get offset column offset count of columns
end tell
set HelperColumn to cells of column 2 of HelperColumn
set formula of HelperColumn to "=1/not(AND($A1=\"" & fillString & "\",COUNTIF($C$1:$C1,$C1)>" & minCount & "))"
tell HelperColumn
try
delete range entire row of (special cells type cell type formulas value errors)
end try
delete range entire column
end tell
end tell
tell application "Microsoft Excel"
set dataRange to get resize (range "A1:E1") row size (first row index of (get end (cell -1 of column 1 of active sheet) direction toward the top)) column size 5
sort dataRange key1 (column 3 of dataRange) header header yes
end tell
Thanks for the help, but…
this part gave me an error that it “could not get…” and listed every cell in the column
tell application “Microsoft Excel”
tell used range of worksheet object of dataRange
set HelperColumn to get offset column offset count of columns
end tell
set HelperColumn to cells of column 2 of HelperColumn
set formula of HelperColumn to “=1/not(AND($A1="” & fillString & “",COUNTIF($C$1:$C1,$C1)>” & minCount & “))”
tell HelperColumn
try
delete range entire row of (special cells type cell type formulas value errors)
end try
delete range entire column
end tell
end tell
and I need the total of the rows in a size to equal 20, not add 20 rows. So if I already have 13 LG, I need 7 more rows.
Thanks,
Also if I have 8 size SM, I will need 12 more rows
Try changing that section to
tell application "Microsoft Excel"
set HelperColumn to get offset (cells of (used range of worksheet object of dataRange)) column offset count of columns
set HelperColumn to cells of column 2 of HelperColumn
set formula of HelperColumn to "=1/not(AND($A1=\"" & fillString & "\",COUNTIF($C$1:$C1,$C1)>" & minCount & "))"
tell HelperColumn
try
delete range entire row of (special cells type cell type formulas value errors)
end try
delete range entire column
end tell
end tell
(File sizes are smaller when only nessesary cells are formatted, rather than full columns)
Also, the code adds 20 lines of each size and then removes the unnessary ones. The advantage to that approach is that it can be done in bulk, rather than looping through each row of the data base.
So, it will (add 20 rows of LG) and (add 20 rows of Sm) and then (delete 13 of the added LG rows and 7 of the Sm), three actions all without needing to know how many Lg or Sm exits before exicution.
I tried this and now all I get is a time out when I run this, seems like it is getting hung up on the same section especially:
set HelperColumn to get offset (cells of (used range of worksheet object of dataRange)) column offset count of columns
set HelperColumn to cells of column 2 of HelperColumn
any suggestions?
I may have been getting too cute in defining the location of a helper column.
This vesion hard codes the helper column in the third line. Put the address to some helper column to the right of everything else. (Its going to get deleted, so any data to the right of the designated helper column would be shifted left).
I also added some comments.
set minCount to 20
set fillString to "***"
set addressOfHelperColumn to "G1"
-- initial sort
tell application "Microsoft Excel"
set dataRange to get resize (range "A1:E1") row size (first row index of (get end (cell -1 of column 1 of active sheet) direction toward the top)) column size 5
sort dataRange key1 (column 3 of dataRange) header header yes
set sizeList to get value of (get resize (get offset (column 3 of dataRange) row offset 1) row size (count of rows of dataRange) - 1)
end tell
-- get list of sizes
set uniqueSizes to {}
repeat with oneSize in sizeList
set aSize to item 1 of oneSize
if not ({aSize} is in uniqueSizes) then
copy aSize to end of uniqueSizes
end if
end repeat
-- add minCount extra rows for each size
repeat with oneSize in uniqueSizes
if oneSize ≠"" then
tell application "Microsoft Excel"
set oneChunk to get resize (get offset (get end (cell -1 of column 3) direction toward the top) row offset 1) row size minCount
tell entire row of oneChunk
set value of column 1 to fillString
set value of column 3 to oneSize
end tell
end tell
end if
end repeat
-- add formula to helper column
tell application "Microsoft Excel"
set HelperColumn to range addressOfHelperColumn of worksheet object of dataRange
set HelperColumn to get resize HelperColumn row size (first row index of (get end (cell -1 of column 1 of active sheet) direction toward the top))
set formula of HelperColumn to "=1/not(AND($A1=\"" & fillString & "\",COUNTIF($C$1:$C1,$C1)>" & minCount & "))"
tell HelperColumn
-- delete rows where formula returns error value
try
delete range entire row of (special cells type cell type formulas value errors)
end try
delete range entire column
end tell
end tell
-- sort again folding extra rows into the rest
tell application "Microsoft Excel"
set dataRange to get resize (range "A1:E1") row size (first row index of (get end (cell -1 of column 1 of active sheet) direction toward the top)) column size 5
sort dataRange key1 (column 3 of dataRange) header header yes
end tell
Here is a different approach, that loops but doesn’t involve a helper column.
global keyCol
global dataRange
global wb
--set constants
set minCount to 5
set fillString to "--"
-- set ranges
tell application "Microsoft Excel"
set keyCol to range "C:C" of worksheet "Sheet 1" of workbook "ScriptBook.xls"
set wb to worksheet object of keyCol
set dataRange to my getDataRange("A1:E1")
end tell
-- sort data
my initialSort()
-- insert needed rows
tell application "Microsoft Excel"
set topCell to cells of row 2 of keyCol
repeat while value of topCell ≠""
set rowcount to 1
repeat while value of (get offset (topCell) row offset rowcount) = value of topCell
log (get address of (get offset (topCell) row offset rowcount))
set rowcount to rowcount + 1
end repeat
set lastCell to (get offset (topCell) row offset rowcount - 1)
if rowcount < minCount then
insert into range (entire row of (get resize (get offset lastCell row offset 1) row size (minCount - rowcount)))
tell (intersect range1 entire column of dataRange range2 entire row of (get resize (get offset lastCell row offset 1) row size (minCount - rowcount)))
set value of column 1 to fillString
set value of column 3 to value of topCell as string
set rowcount to minCount
end tell
end if
set topCell to get offset topCell row offset rowcount
end repeat
end tell
on initialSort()
tell application "Microsoft Excel"
sort dataRange key1 (my keyDataCol()) header header yes
end tell
end initialSort
on keyDataCol()
tell application "Microsoft Excel"
return intersect range1 keyCol range2 dataRange
end tell
end keyDataCol
on getDataRange(AddressString)
tell application "Microsoft Excel"
set dRange to get range AddressString of wb
set dRange to get resize (dRange) row size (first row index of (get end (cell -1 of column 1 of active sheet) direction toward the top)) column size 5
return dRange
end tell
end getDataRange
thank you, that works a lot better now.
One last issue, when it inserts the sizes for “02”, “04”, basically anything with a “0” in front, it removes the zero. then it does not sort right.
I can not find anything that will correct for this. I tried to tell it to add an apostrophe in front so the zero shows up but it screws up other parts. I cant find anything that would correct for this.
any suggestions?
BTW, I appreciate all your help on this.
Thanks,
Scott
Are you treating the 02 as a number or as text.
If a number, the cells could be formatted “00” (no quotes) to show it with two places.
If you are treating it as text, the cells could be formatted as text before import.
What is the script that adds it to a cell, it should be possible to add a leading ’
the numbers are formatted as text. All that I do is take a master excel document and then copy and paste the columns that I need into a new document.
I can post that script here if you would like…
oddly enough when the script returns the list of sizes it appears this way:
Result:
item 19 of {10.0, 12.0, 14.0, 16.0, " ", “02”, “04”, “06”, “08”, “LG”, “MD”, “ML”, “NO”, "P ", “PS”, “PT”, “SM”, “XL”, “XP”}
I do not know why the number sizes 10.0, 12.0… appear without the"". why would they not appear as “10”
for some reason it is changing the value from “02” to 2.0, I have it going back through and changing the values back with:
tell application “Microsoft Excel”
set numCells to first row index of (get end (cell 1 of row (count rows)) direction toward the top)
repeat with i from 1 to numCells
if the value of (cell 3 of row i) = 2.0 then
set value of (cell 3 of row i) to “'02”
else
if the value of (cell 3 of row i) = 4.0 then
set value of (cell 3 of row i) to “'04”
else
if the value of (cell 3 of row i) = 6.0 then
set value of (cell 3 of row i) to “'06”
else
if the value of (cell 3 of row i) = 8.0 then
set value of (cell 3 of row i) to “'08”
end if
end if
end if
end if
end repeat
end tell
this obviously takes a long time to do, so if you have an easier way of doing this that would be great.
Thanks,
Scott
try changing this one line in the – insert needed rows section
tell (intersect range1 entire column of dataRange range2 entire row of (get resize (get offset lastCell row offset 1) row size (minCount - rowcount)))
set value of column 1 to fillString
set value of column 3 to "'" & (value of topCell as string) -- <<<<
set rowcount to minCount
end tell
BTW, the difficult to read bit is quote apostrophy quote
Overall, I think its a case of Excel being overly helpful in converting data entry.
Is your column C set to the “text” number format?
sorry, I did not see the previous post where you included this part of the script. I am using the script with the helper column. Everything is working in that one. I tried the other and I get errors on this part:
set topCell to cells of row 2 of keyCol
repeat while value of topCell ≠“”
says it cant get value of empty cell…
also I have tried adding a “'” but that screws up the numbers 10, 12, 14
any suggestions for the one with the helper column.
Since the helper column routine from post #9 is working for you, I’ll stick with that.
It looks like the trouble sizes are the ones that start with zero, so I altered the – add minCount extra rows for each size section.
set minCount to 20
set fillString to "***"
set addressOfHelperColumn to "G1"
-- initial sort
tell application "Microsoft Excel"
set dataRange to get resize (range "A1:E1") row size (first row index of (get end (cell -1 of column 1 of active sheet) direction toward the top)) column size 5
sort dataRange key1 (column 3 of dataRange) header header yes
set sizeList to get value of (get resize (get offset (column 3 of dataRange) row offset 1) row size (count of rows of dataRange) - 1)
end tell
-- get list of sizes
set uniqueSizes to {}
repeat with oneSize in sizeList
set aSize to "" & item 1 of oneSize
if not ({aSize} is in uniqueSizes) then
copy aSize to end of uniqueSizes
end if
end repeat
-- add minCount extra rows for each size
repeat with oneSize in uniqueSizes
if oneSize ≠"" then
tell application "Microsoft Excel"
set oneChunk to get resize (get offset (get end (cell -1 of column 3) direction toward the top) row offset 1) row size minCount
tell entire row of oneChunk
set value of column 1 to fillString
if character 1 of oneSize = "0" then
set value of column 3 to "'" & oneSize
else
set value of column 3 to oneSize
end if
end tell
end tell
end if
end repeat
-- add formula to helper column
tell application "Microsoft Excel"
set HelperColumn to range addressOfHelperColumn of worksheet object of dataRange
set HelperColumn to get resize HelperColumn row size (first row index of (get end (cell -1 of column 1 of active sheet) direction toward the top))
set formula of HelperColumn to "=1/not(AND($A1=\"" & fillString & "\",COUNTIF($C$1:$C1,$C1)>" & minCount & "))"
tell HelperColumn
-- delete rows where formula returns error value
try
delete range entire row of (special cells type cell type formulas value errors)
end try
delete range entire column
end tell
end tell
-- sort again folding extra rows into the rest
tell application "Microsoft Excel"
set dataRange to get resize (range "A1:E1") row size (first row index of (get end (cell -1 of column 1 of active sheet) direction toward the top)) column size 5
sort dataRange key1 (column 3 of dataRange) header header yes
end tell
Thanks for all the help on this. It is still messing with the numbers though. When the value is 10 the script makes the new row as “10.0” and then it will not sort the correct way because it sees it as two separate values.
So in order to not wear out my welcome on this, I do have a way to change the value by checking every cell and changing it’s value.
Once again, I appreciate the help
after running the script, the cell showing “10.0” , let’s say that that cell is C10.
What does “=ISNUMBER(C10)” (in some spare cell) return?
What is the formatting of C10?
In your original file, is 10 stored as a number or as text?