How can I find duplicates in Numbers?

What sort of data are you dealing with?

Jerry

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.

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:

Regards,

Barry

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."

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

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.

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 tomakedelimitedstring(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

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

SG

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.

Hi writer_in_virginia,

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

SG

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

SB

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

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

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

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

It's all too easy for gremlins to creep in when copying and pasting from a forum like this. All it takes is one little one.

Glad it's working there now!

SG