0

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

  • 2
    If this is a one time thing and based on the image in your question, I'd simply insert IF(A2=B2,"DUPE","") as a new formula in C2... then with C2 selected press Command-C to copy the formula... then scroll down to the last row containing info and press the Shift key while clicking the last cell in column C to select the range and press Control-C to paste the formula. Now with column C having "DUPE" where appropriate, I'd then sort the sheet on column C and highlight the rows with "DUPE" in them and delete them. Then select column C and press delete to remove the formula. – user3439894 Nov 12 '18 at 20:13
  • That should have been Control-V to paste the formula. (That's what I get for copying and pasting.) – user3439894 Nov 13 '18 at 15:29
  • Thanks, this should work, but all I get is the formula in each cell in column C and they don't calculate. – BlueDogRanch Nov 13 '18 at 16:34
  • You said, "this should work, but all I get is the formula in each cell in column C and they don't calculate", That's because you are not copying it properly, After using Numbers > Insert > Formula > New Formula and pasting the formula in the cell you press Enter, then re-select the cell you pasted the formula into and then press Command-C to copy. In other words, the Formula window should not be open when making the copy after the original paste. – user3439894 Nov 13 '18 at 16:58
2

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!)

  • 1
    Nice shade of pink! :) +1 – user3439894 Nov 12 '18 at 21:34
  • Thanks, this works great and is a good example for working with Numbers. – BlueDogRanch Nov 13 '18 at 16:34

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .