Removing Duplicates in Numbers

Scripting Forums AppleScript | Mac OS X
#1

Hi,

Is there a way to remove rows in Numbers using Applescript if duplicate values are present in a column?

For example, (table below) script cycles through date column and if it finds a duplicate date, it removes that row?

Shopping Price Date

Lime 99 010417
Pear 86 040417
Grape 55 030417
Orange 40 040417
Lemon 99 030417
Peach 99 040417

#2

I’m not using Numbers but in Excel you can remove duplicate values from a range. It’s a long shot but since Numbers is a (poor) copy of Excel maybe there is a similar command.

#3

This short script does the job.

set valuesFound to {}

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	set rowNum to 3 # I'm lazy so I skip the two higher rows
	tell column 3
		repeat
			set aVal to value of cell rowNum
			if aVal is not in valuesFound then
				set end of valuesFound to aVal
				set rowNum to rowNum + 1 # Jump to next row
			else
				remove row rowNum
				 # Don't increment rowNum
			end if
			if rowNum > (count rows) then exit repeat
		end repeat
		# instruction useful if several blank rows were at bottom of the table
		if value of cell -1 is missing value then remove row -1
	end tell
end tell

Yvan KOENIG running Sierra 10.12.6 in French (VALLAURIS, France) mercredi 26 juillet 2017 15:37:13

#4

Exactly what I needed, perfect.

Thanks Yvan!

#5

Hi Adam

Thanks for the feedback but I enhanced the code a bit a bit :

set valuesFound to {missing value} # So that there will not be a remaining blank row
set rowsToRemove to {}


tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	tell column 3
		repeat with r from 3 to (count rows)
			set aVal to value of cell r
			if aVal is not in valuesFound then
				set end of valuesFound to aVal
			else
				set end of rowsToRemove to r
			end if
		end repeat
		set rowsToRemove to reverse of rowsToRemove
	end tell
	repeat with r in rowsToRemove
		remove row r
	end repeat
end tell

If you prefer to keep the lower rows, use :

set valuesFound to {missing value} # So that there will not be a remaining blank row
set rowsToRemove to {}


tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	tell column 3
		repeat with r from (count rows) to 3 by -1
			set aVal to value of cell r
			if aVal is not in valuesFound then
				set end of valuesFound to aVal
			else
				set end of rowsToRemove to r
			end if
		end repeat
		
	end tell
	repeat with r in rowsToRemove
		remove row r
	end repeat
end tell

Yvan KOENIG running Sierra 10.12.6 in French (VALLAURIS, France) mercredi 26 juillet 2017 21:12:05