How to find duplicate adjacent cells in Numbers

applescriptautomationiworknumbers

I have a Numbers sheet that is thousands of rows long. As an example in the image, the sheet has two columns with URLs. What I need to do is find the rows that have the same URL in the two adjacent cells and then change the background color of that row so I can identify the rows and delete them; or write the word "dupe" to column C. In the example, row 4 has the same URL the two cells. How would I flag or mark rows like that in the entire sheet? With a calculation? Or an Applescript/Automator?

enter image description here

Best Answer

Here is an AppleScript method, which demonstrates three different types of action taken upon the rows with duplicate cell entries in columns "A" and "B" (equivalent to cells 1 and 2):

use N : application "Numbers"
--------------------------------------------------------------------------------
# PROPERTY VALUES & GLOBAL VARIABLES
property document : a reference to document 1 of N
property sheet : a reference to active sheet of my document
property table : a reference to table 1 of my sheet

global them
--------------------------------------------------------------------------------
# IMPLEMENTATION:
on run
    if not (exists my table) then return false

    set them to a reference to (every row of my table ¬
        where the value of cell 1 = the value of cell 2 ¬
        and the value of cell 1 ≠ missing value)

    highlight()
    ---OR:
    -- comment()
    --OR:
    -- delete -- WARNING: permanent!
end run
--------------------------------------------------------------------------------
# HANDLERS:
to highlight()
    set the background color of them to ¬
        {65535, 65535 / 4, 65535 / 2}
end highlight

to delete
    delete them
end delete

to comment()
    set the value of cell 3 of them to "SNAP!"
end comment
---------------------------------------------------------------------------❮END❯

I've defined three handlers, highlight(), comment(), and delete, each of which, if called, will perform a particular action upon the rows of interest. Currently, you can see in the script a few lines within the section labelled IMPLEMENTATION: that I've set it currently to perform the highlight() action, which will change the particular rows' background colour to a shade of pink I like.

Below that line are commands that I have commented out using --, so currently they remain inert. When uncommented, the comment() handler will enter the word "SNAP!" into the third column of each row of interest; and the delete command (note the lack of parentheses for this one) will simply delete the rows completely. I've marked this with a warning that implies permanent deletion, although in truth, you can recall the rows back into existence using the application's builtin Undo menu item, or ⌘Z (on a one-by-one basis!)

Related Question