How can I find duplicates in Numbers?

Thanks for this it worked great! What would I need to change for it to do a spreadsheet? I have an 4 columns in my spreadsheet, email, first name, last name and owner. I need these rows to stay together but want to delete the duplicates.

Thanks again!

Do you mean you have rows where the values all four columns are duplicates of another row? Or are you looking for duplicates on one column only and want to remove the whole row when you encounter a duplicate?

SG

All four columns are duplicates of another row. This is a list of customers and some of the customers are entered several times, so I want to remove one whole row that duplicates another.

Thanks!!

If you're only doing this occasionally, you could do this:

Use the script to copy paste the distinct values from column A into column A of a new table.

[Edit... will fill in rest. Had wrong instructions.]

SG

If you are doing this occasionally, you could do this:

1. Set up a new empty table with the same structure as the first
2. Select the body cells in column A of your first table
3. Run the script upthread
4. Paste result into body cells of column B of your new table
5. Use a VLOOKUP to fill in the rest of the cells in the new table
6. Select all cells in the new table, command-c to copy, and Edit > Paste Formula Results. (This removes the formulas).

Here, this looks like this:

The formula in cell B2, copied right and down, is:

=VLOOKUP($A2,Table 1::$A:$D,COLUMN(),FALSE)

If you are doing this a lot in different sheets or documents then it would not be hard to have an AppleScript generate a new table. But this way, using the script and one VLOOKUP formula, is probably the easiest if you don't have to do this that often.

SG

Thank you! I am not sure if I am doing something wrong but when I run the script on column A in table 1 and paste it into column A in table 2 it works perfect, the duplicates are gone. But when I use the index function the rows don't line up, it keeps the duplicate names so the email addresses no longer match to the correct first, last and owner. Sorry if I am not explaining myself clearly, I hope this makes sense.

Thanks!

But when I use the index function the rows don't line up, it keeps the duplicate names so the email addresses no longer match to the correct first, last and owner.

That's right. I misled you. Sorry about that INDEX formula; I wasn't quick enough to edit that post. Use the VLOOKUP in my revised post.

SG

Yes, that worked!! Thank you SOOOO much!

Thankyou so so much, my duplicate numbers are now highlighted! is there also a way to work out missing numbers from a range of numbers, say 1-100?

Hi k8ty,

"is there also a way to work out missing numbers from a range of numbers, say 1-100?"

You could put a count of each possible value into a table. In the sample below, I've used a range of 0-99 rather than 1-100. Values shown in the Data table are randomly generated. The count of each value is shown in the Count table. "Empty" cells actually contain a count of zero; conditional formatting is used to change the text colour to white, matching the cell fill colour, if the value is zero. The highest count, 4, was for the value 49, detemined by adding the value at the top of the column (40) to the value at the left of the row (9).

Regards,

Barry

is there also a way to work out missing numbers from a range of numbers, say 1-100

Hi k8ty68,

AppleScript is pretty good for solving this kind of problem. Here is an example that checks the values in your selection range against a range from 1-100 and places the "missing" values on the clipboard for pasting wherever you want.

This assumes integers. If you have other values you want to check against you need to build matchList with something like:

set matchList to {1.1, 2.3, 5.1, 4.4, 5...........}

To use, paste the script into AppleScript Editor, select cells in a Numbers 3 table, run the script, click once in a cell where you want your missing values to appear in a column, and command-v to paste. If you need this as a service you can paste it into a Run AppleScript action in Automator. If you have trouble with that, post, and I'll explain further how to do that.

SG

--select range in Numbers 3 table, run, paste values not in match list

--https://discussions.apple.com/thread/5180288?answerId=25190384022#25190384022&ac_cid=tw123456#25190384

property rangeStart : 1

property rangeEnd : 100

--1. build list of values (here numbers are specified) to check against

set matchList to {}

repeat with i from rangeStart to rangeEnd

copy i as number to end of matchList

end repeat

--2. make list of values in selection range

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

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

tell selectedTable to tell the selection range

set valuesList to {}

repeat with i from 1 to count cells

set thisValue to value of cell i as text

if thisValue is not "missing value" then copy thisValue as number to end of valuesList

end repeat

end tell

end tell

--3. Go through each value in matchList and list it if it is not in valuesList

set missingList to {}

repeat with i from 1 to count matchList

tell item i of matchList

if it is not in valuesList then

copy it to end of missingList

end if

end tell

end repeat

--4. Convert missingList to string for pasting

set pasteStr to joinList(missingList, return) --change return to tab if paste horizontal

set the clipboard topasteStr

display notification "Ready to paste " & (count missingList) & " missing values."

return pasteStr

--handler to convert list to string

to joinList(aList, separator) --convert AS list to delimited string

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

set lstStr to aList as string

set text item delimiters of AppleScript to oTid

return lstStr

end joinList

--end of script

And here's another way, not requiring AppleScript to identify missing values, though it does require Numbers 3. I've used a range of 1-10.

The following formula forces the values in Table 1 into a one-dimensional array (1-column) with this formula, using a the UNION.RANGES function new to Numbers 3.

=INDEX(UNION.RANGES(FALSE,Table 1::$A$1,Table 1::$A$2:$C$10),1,ROW())

Then in the Match table just list in column A the values in the range against which you want to check for missing values and in column B use a garden-variety lookup formula to look for values in the other table, and if they are not there, generate an error that here displays a "Missing" message.

=IFERROR(VLOOKUP(A2,Values 1D::$A,1,FALSE),"Missing")

SG

This version eliminates the Values ID table of SG's solution above. Counts (and the "Missing" tag for counts of zero) are calculated directly in the Match table:

Table 2::B2, and filled down:

=IF(COUNTIF(Table 1 :: $A$2:$C$10,A)<1,"Missing",COUNTIF(Table 1 :: $A$2:$C$10,A))

Values 1 to 10 used in the example to fit here. Easily extendible to 1-100.

Regards,

Barry

Yes, thanks for the example. Definitely easier with just the two tables. With Numbers 3 the AppleScript solution is easier still, especially if this kind of checking for missing values may be needed for other tables, sheets, or documents. Just paste the script into AppleScript Editor, select the range of cells in a table, run, paste the results wherever wanted.

SG

Thanks, this was the easiest for me! can this be put in the automater like the other applescript?

Im a complete beginner, so apologies for the hassle, i appreaciate yr help lots!😉