jjfrosty1

Q: How can I find duplicates in Numbers?

How can I find duplicates in Numbers?

Do I search each field or is there a short cut?

iMac (21.5-inch Mid 2011), OS X Mountain Lion (10.8.4)

Posted on Jul 19, 2013 9:47 AM

Close

Q: How can I find duplicates in Numbers?

  • All replies
  • Helpful answers

Page 1 of 5 last Next
  • by Jerrold Green1,

    Jerrold Green1 Jerrold Green1 Jul 19, 2013 12:53 PM in response to jjfrosty1
    Level 7 (30,001 points)
    Jul 19, 2013 12:53 PM in response to jjfrosty1

    What sort of data are you dealing with?

     

    Jerry

  • by jjfrosty1,

    jjfrosty1 jjfrosty1 Jul 19, 2013 6:56 PM in response to Jerrold Green1
    Level 1 (0 points)
    Jul 19, 2013 6:56 PM in response to Jerrold Green1

    email addresses

  • by Barry,

    Barry Barry Jul 19, 2013 10:06 PM in response to jjfrosty1
    Level 7 (32,697 points)
    iWork
    Jul 19, 2013 10:06 PM in response to jjfrosty1

    Hi jj,

     

    For the example, I'll assume the email addresses are in column B, starting in B2.

     

    Insert a column to the right of column B (Click on any cell in column B, then press option-right arrow). This will become column C.

     

    In C2, enter this formula: =COUNTIF($B$2:B2,B2)

     

    then fill it down the rest of the column.

    Screen Shot 2013-07-19 at 10.00.58 PM.png

    The formula counts the occurrences of the address in the current row from row 2 to the current row.

    Any row with a result greater than 1 indicates the address in that row is a duplicate.

     

    You can also mark those rows using a conditional formatting rule:

    Screen Shot 2013-07-19 at 10.04.56 PM.png

     

    Regards,

    Barry

  • by AMSapple,

    AMSapple AMSapple Feb 3, 2014 8:11 AM in response to jjfrosty1
    Level 1 (0 points)
    Feb 3, 2014 8:11 AM in response to jjfrosty1

    Hello - Barry provided this formula (below) for identifying duplicate emails in a Numbers sheet. How can I apply this formula to each row in a column automatically without having to adjust the formula manually for each row? Thanks!

     

     

    "In C2, enter this formula: =COUNTIF($B$2:B2,B2)

     

    then fill it down the rest of the column."

  • by Wayne Contello,

    Wayne Contello Wayne Contello Feb 3, 2014 8:37 AM in response to AMSapple
    Level 6 (19,416 points)
    iWork
    Feb 3, 2014 8:37 AM in response to AMSapple

    select cell C2, copy

     

    select column C (by clicking the column header... the letter "C" at the top), then

    hold the command key, then click cell "C1" to unselect the header

    paste

  • by SGIII,

    SGIII SGIII Feb 3, 2014 2:00 PM in response to jjfrosty1
    Level 6 (10,796 points)
    Mac OS X
    Feb 3, 2014 2:00 PM in response to jjfrosty1

    If your ultimate goal is to derive a list with duplicates removed (rather than just the first step of listing or highlighting the duplicates) the following script can help. To use, just select the range of cells with the duplicates (in this case the cells in your eMail column), run the script, then paste the results wherever you want. To do that, just click a cell once and command-v (Edit > Paste) or option-shift-command-v (Edit > Paste and Match Style).

     

    It can be run from AppleScript Editor or placed in the usual script menus. But to make it just a click or two away, in Automator I created a new Service, dragged a Run AppleScript action into the right pane and copy-pasted the script in, replacing all the default text, then set 'Service receives' at the top to 'no input' and 'in:' to 'Numbers' and saved it with the name I wanted it to have in my menu. Here it is in my Services menu.  To use it now all I have to do is select cells and make a menu pick, then click a cell at the top of my destination area and paste.

     

     

    Screen Shot 2014-02-03 at 4.46.48 PM.png

     

     

    This is the script:

     

    --select range with duplicates, run this, paste distinct values

    try

              tell application "Numbers" to tell front document to tell active sheet

                        set selected_table to first table whose class of selection range is range

                        tell selected_table to set selected_values to value of every cell of selection range

              end tell

              set distinct_values to deduplicate(selected_values)

              if (count distinct_values) < 1 then error

      set the clipboard to makedelimitedstring(distinct_values, return) --tab for horizontal

              display notification "Ready to paste distinct values" with title "Numbers"

    on error

              display alert "Select some non-blank cells and try again." buttons {"Cancel"}

    end try

     

    to deduplicate(old_list) -- make new list without duplicates or blanks

              set new_list to {}

              repeat with this_item in old_list

                        set not_a_duplicate to this_item is not in new_list

                        set not_a_blank to contents of this_item is not missing value

                        if not_a_duplicate and not_a_blank then set end of new_list to (contents of this_item)

              end repeat

              return new_list

    end deduplicate

     

    to makedelimitedstring(a_list, delimiter) --convert AS list to delimited string

              set otid to text item delimiters of AppleScript

              set text item delimiters of AppleScript to delimiter

              set delimited_str to a_list as string

              set text item delimiters of AppleScript to otid

              return delimited_str

    end makedelimitedstring

    --end of script

     

     

    SG

  • by SGIII,

    SGIII SGIII Feb 3, 2014 7:49 PM in response to jjfrosty1
    Level 6 (10,796 points)
    Mac OS X
    Feb 3, 2014 7:49 PM in response to jjfrosty1

    Here's a script that (in Numbers 3.1) will turn the text of duplicates red. Select cells in the email column (it will work on data spread across multiple columns as well), then run the script.  That's it.

     

    --select range, run, turns duplicates red

    tell application "Numbers" to tell front document to tell active sheet

              set selected_table to first table whose class of selection range is range

              tell selected_table

                        set selected_range to the selection range

                        tell selected_range

                                  set values_list to {}

                                  repeat with i from 1 to count cells

                                            if values_list contains (value of cell i as text) then set text color of cell i to "red"

                                            set end of values_list to value of cell i as text

                                  end repeat

                        end tell

              end tell

    end tell

    --end of script

     

     

     

    Screen Shot 2014-02-03 at 10.49.27 PM.png

     

    SG

  • by writer_in_virginia,

    writer_in_virginia writer_in_virginia Feb 18, 2014 3:29 PM in response to SGIII
    Level 1 (0 points)
    Feb 18, 2014 3:29 PM in response to SGIII

    Thank you SO much. Your script titled (--select range with duplicates, run this, paste distinct values) and accompanying advice works fantastically well. Followed your instructions -- even though a complete AppleScript novice, followed by success in seconds. No more duplicates. They're gone.

  • by SGIII,

    SGIII SGIII Feb 18, 2014 5:35 PM in response to writer_in_virginia
    Level 6 (10,796 points)
    Mac OS X
    Feb 18, 2014 5:35 PM in response to writer_in_virginia

    Hi writer_in_virginia,

     

    It's gratifying to know that the script has helped. Thanks for the feedback!

     

    SG

  • by writer_in_virginia,

    writer_in_virginia writer_in_virginia Feb 21, 2014 2:55 PM in response to SGIII
    Level 1 (0 points)
    Feb 21, 2014 2:55 PM in response to SGIII

    The script for turning duplicates RED would not run. It said it encountered an error. I thought I installed it correctly with Automator.

     

    SB

  • by SGIII,

    SGIII SGIII Feb 21, 2014 3:08 PM in response to writer_in_virginia
    Level 6 (10,796 points)
    Mac OS X
    Feb 21, 2014 3:08 PM in response to writer_in_virginia

    I haven't built any error trapping in the script. But I tried it again by copying and pasting from the post in this thread, and it's working here.

     

    The most likely causes of an error are:

     

    -- You have Numbers 2.3 open along with Numbers 3.1. Only Numbers 3 can be open.

     

    --You forgot to select the cells you want to check for duplicates before running it.

     

    --In Automator you don't have 'Service receives' set to 'no input' or you didn't replace all the default text in Automator with the script (i.e., you left "on run... run" in there).

     

    The script doesn't have to be in Automator to run. You can also paste it into AppleScript Editor, select cells, and run it that way to see what happens.

     

    If you keep getting an error perhaps you could post the message you are getting.

     

    SG

  • by writer_in_virginia,

    writer_in_virginia writer_in_virginia Feb 21, 2014 4:48 PM in response to SGIII
    Level 1 (0 points)
    Feb 21, 2014 4:48 PM in response to SGIII

    Here are the error messages. The first one is: The action "Run Applescript" encountered an error.

     

    The second error message, when I ran it from the Applescript editor, was this.... Syntax Error.  Expected "end" or "end tell" but found unknown token.

     

    -- Am running Numbers 3.1 only. Don't have any older versions.

     

    – Selected cells.

     

    – Service was set to  "no imput". Default text was replaced.

     

    SB

  • by SGIII,

    SGIII SGIII Feb 21, 2014 5:22 PM in response to writer_in_virginia
    Level 6 (10,796 points)
    Mac OS X
    Feb 21, 2014 5:22 PM in response to writer_in_virginia

    If you're getting that error in AppleScript Editor then you may have missed part of the script. Try the copy and paste again:

     

    From and including the:

     

    --select range

     

    through and including the:

     

    --end of script

     

    Make sure you get the double --.  Those tell AppleScript that the line is just a comment, not part of the script.

     

    SG

  • by writer_in_virginia,

    writer_in_virginia writer_in_virginia Feb 21, 2014 5:42 PM in response to SGIII
    Level 1 (0 points)
    Feb 21, 2014 5:42 PM in response to SGIII

    Now it works! I thought I had been meticulous. Double-dashes were in the failed one.

     

    Finally, went back to Apple Discussions and repasted fresh from your original. Have no idea why the repaste worked, but it sure did.

     

    Thank you for your patience and time, especially on a FRI evening.

     

    writer_in_virginia

Page 1 of 5 last Next