How can I find duplicates in Numbers?

Here it is in a Get Missing Values Automator Service. (Dropbox dowload)

Usage is as described above: select the cells, run it by making a pick from the Services menu, paste the values wherever you want.

If you want to change the range from 1-100 to something else you can open the workflow in Automator and change the numbers after rangeStart and rangeEnd at the top of the script. To open it in Automator you can hold down the option key in Finder and in the menu Go > Library > Services, then doubleclick the .workflow package. If you need to delete it from your Services menu just send the .workflow package to the trash the way you would any other Finder item.

BTW, making an Automator Service from an AppleScript is quick and easy. Just open Automator, File > New, choose 'Service' as the type of document, drag a Run AppleScript action from the left into the right pane (to find that action just type 'apple' into the search box), and paste the script into the action, in this case replacing all the default code that Automator suggests. Choose 'No Input' after 'Service receives' and choose 'Numbers.app' after 'in' and save. It should then automatically show up in yur Services menu.

SG

You are awesome!!! Thankyou so much! 😀

Is there an Applescript I can use to remove duplicates in the following way -- a purge. Here is the scenario...

In row B is a list of active URLS. In the next row, Row C, is a list of URLS that became inactive.

How can I pass the list of Inactive URLS in Row C against the good URLS in Row B and do the following -- If an entry in the In-active URLS matches an entry in the ACTIVE URLS, delete that entry from the Actives.

In other words, I need to purge the Active URLS of any that may be inactive.

What script could I use to accomplish this?

Thank you so much.

writer_in_virginia

There is a script you can try posted by SG earlier in this same thread. Review that and see if it helps you. You can use the navigation arrows and numbers at the top and bottom of this thread to go to other pages in this thread:

Of if the script does not do what you want you can do something like this:

Add a new column after the list is inactive URLS (in this example, column D).

D2=IF(COUNTIF($C, B2)>0, "", B2)

this is shorthand for select cell D2, then type (or copy and paste from here) the formula:

=IF(COUNTIF($C, B2)>0, "", B2)

select cell D2, copy

select column D, paste

now column D ONLY contains valid URLs. You can copy it, then select column B, then select the menu item "Edit > Paste Formula Results", now empty the inactive URL list and repeat later as needed

I tried to locate that script and couldn't find it. Also the last sentence of your response was cut off. It said...

"Or if the script does not do what you want you can do something like this:", but there was nothing underneath that line.

Just saw your full suggestion come up. Will try it right now. Thanks

I apologize for a super-dumb question, but being a novice, I didn't know how to enter this formula in row D. I put in an equal sign and then added the formula to the whole D row, but that wasn't right. Got not result and error message. Any advice? Thank you.

No such thing as a super-dumb question with spreadsheets. They can be complicated. I think Wayne will answer on your formula entry problem.

Meanwhile, here is a script you can paste into AppleScript Editor and run. It puts the results on the clipboard so you can paste the new list where you want it via command-v or Edit > Paste and Match Style. It assumes you have clicked somewhere on the table. You can change the numbers in the "Properties" if you decide to put your lists in columns other than B and C.

SG

-- active list in one column, inactive list in another

-- produces a new list with inactive values removed

-- run and paste results where needed

property colNumActive : 2 -- change if Active List in column other than B

property colNumInactive : 3 -- change if Inactive List in column other than C

property firstBodyRow : 2 -- change if other than 1 header row

set activeList to getNonBlankColValues(colNumActive)

set inactiveList to getNonBlankColValues(colNumInactive)

--loop through activeList removing any matches with inactiveList

set newList to {}

repeat with i from 1 to count of activeList

tell item i of activeList

if it is not in inactiveList then copy it to end of newList

end tell

end repeat

set the clipboard tojoinList(newList, return)

display notification "Cleaned list ready to paste."

to getNonBlankColValues(colNum)

try

tell application "Numbers" to tell front document to tell active sheet to tell (first table whose class of selection range is range) to tell column colNum

set colVals to {}

repeat with i from firstBodyRow to count cells

tell cell i

if value is not missing value then copy value to end of colVals

end tell

end repeat

return colVals

end tell

on error

display dialog "Having trouble. Did you select a table that has values in columns B and C?" buttons "Cancel" default button "Cancel"

return

end try

end getNonBlankColValues

to joinList(aList, separator)

set {oTID, AppleScript'stext item delimiters} to {AppleScript'stext item delimiters, separator}

set listStr to aList as string

set AppleScript'stext item delimiters to oTID

return listStr

end joinList

-- end of script

ENTERS A FORMULA IN A SINGLE CELL:

D2=IF(COUNTIF($C, B2)>0, "", B2)

<<Pay particular attention here>>

this is shorthand for select cell D2, then type (or copy and paste from here) the formula:

=IF(COUNTIF($C, B2)>0, "", B2)

NOW DUPLICATE THE FORMULA IN THE WHOLE COLUMN

select cell D2, copy

select column D, paste

FINALLY, REPLACE THE ORIGNAL LIST WITH ONLY ACTIVE URLS:

now column D ONLY contains valid URLs. You can copy it, then select column B, then select the menu item "Edit > Paste Formula Results", now empty the inactive URL list and repeat later as needed

This script is magic. Seems like it anyway. Thank you so much! Now I have to go to Wayne's method and master that too.

Thank you both for sharing your abilities.

When I start by entering the formula =IF(COUNTIF($C, B2)>0, "", B2) in cell D2, it then says (after clicking on the green check mark), "This formula contains a syntax error."

Am trying to follow your instructions to the letter, so I don't know where I got it wrong.

I understand your instruction:

NOW DUPLICATE THE FORMULA IN THE WHOLE COLUMN

select cell D2, copy

select column D, paste

It's just the first step before that which I'm not getting right.

Writer,

Here is how the formula looks in a cell on my computer:

Here is what you posted:

=IF(COUNTIF($C, B2)>0, "", B2)

Here is what I suggested you type:

=IF(COUNTIF($C, B2)>0, "", B2)

I do not see any difference. Can you, please, post a screenshot? Here's how:

https://discussions.apple.com/docs/DOC-6591

Here's the screenshot.

you have an extra "=" sign

Thank you. Corrected that.

Here's what I see now...

This immediately computes a value for the D2 cell.

Now, when I go to select entire D row, I get either the computed value of that cell duped all the way down -- or only a D2 cell with the text code of the formula -- as in screenshot below. I must have done something else off.