AppleScript for Excel development #2

In this post I mentioned that I was working on an AppleScript to handle an Excel process. I decided to abandon the nested line-by-line evaluation of the two necessary columns as this took entirely too long. In this particular process, I have not yet discovered a way to handle a way around this step, but I have taken a different approach to help minimize the cycles spent.

I managed to get the autofill function to script properly. It was a bit fiddly at first, but once you figure out all the syntax, it becomes more agreeable. This allows me to insert a formula in one cell and populate the rest of the column with the same function. By referencing ranges instead of specific cell address', I can assure that my functions always work, regardless of location. The first instance concatenates the values of the two mentioned columns into one cell, making a combined page & reference number.

5_29460
5_29460
5_30012

The next pass sorts the sheet by this column and then inserts another formula that compares each cell to the one above it, looking for a match. Again, autofill populates the column in seconds. When the compare returns true, the cell takes on a specified value. Afterwards, both columns are copied and pasted back in with the values only (Copy & Paste Special: Values).

The compare column is sorted by value and we begin a loop that handles the inevitable line-by-line evaluation. To control this, I included a single Boolean variable as an exit value. The script will cycle through each row and evaluate the cell. If the cell indicates a previously matched row (duplicate), it deletes the row. Once it reaches a unique row, either indicated by an empty string or a second pre-defined value, it switches the Boolean value, triggering the loop escape. This keeps the script from having to evaluate the rows that are already known to be either unique or the first instance of a row.