Everyone is how to delete repeating values?

Hi Tao,

If you wish to delete the rows containing duplicate entries, this may work for you.

Step 1: Add a column to the table to mark the duplicate entries. Column B is used in this table.

Formula: Enter in B2 and fill down.

B2: =IF(COUNTIF($A$2:A2,A)>1,"DELETE","keep")

!! After filling down, and while all cells in column B are still selected:

Copy.

Edit > Paste Values (OR Paste Formula results)

Ths replaces the formula with the results shown.

Step 2: Sort the table on column B to bring all DELETE results together.

Step 3: delete the rows marked for deletion.

Select the cells containing DELETE.

Go Table > Delete Rows.

Done.

Regards,

Barry

This will mark the rows as shown in Step 1

You can add an extra column that included distinct values (an omits duplicates) like this:

the first row is a header.

C2=IF(ISBLANK(B2), "", IF(IFERROR(MATCH(B2,$C1:C1,0), 0)=0, B2, ""))

select C2, copy

select column C by click the letter "C" in the column header

now hold the <command> key and click cell C1 to unselect it

paste

if you want to replace the original data (in this example the original data is in column B) select column C, copy, then select column B and use the menu item "Edit > Paste Formula Results"

Hi Tao,

I've found that a short AppleScript provides a convenient way to identify duplicates (repeating values) in a range.

Just select the range of cells you want to check for duplicates and click. It will turn the duplicates red.

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

set this_value to value of cell i as text

if values_list contains this_value then set text color of cell i to "red"

set end of values_list to this_value

end repeat

end tell

end tell

end tell

--end of script

Just copy and paste the above script into AppleScript Editor, and run it from there. Or place it in your scripts menu. Or download this Automator Service (Dropbox download) and double-click it to install it in your Services menu.

Similarly, if you want to get a list of just the distinct values in a range (i.e. with duplicates removed) this Copy Distinct Automator Service (Dropbox download) is very convenient.

Select the cells, make the menu pick, then paste the distinct values wherever you want in a table by single-clicking a cell and typing command-v to paste.

These functions become, in essence, a customized menu. That way you don't have to set up formulas to do this each time you work with a new Numbers document.

SG

I assume this question was translated from a different language. The question is not clear.

If you are asking about the feature that pops up a list of previous entries, go to Numbers/Preferences and uncheck "show suggestions when editing cells"

Function is very powerful，just i don't know much about Automator，more difficult to use.

No need to know anything about Automator. Just doubleclick the workflow and it will ask you if you want it to install itself in the Services menu. Then it becomes just like any other menu pick. Select the cells. Then choose from the menu. No formulas. No fuss... And after it's installed it's ready for use with any document, not just the one you are currently working on.

SG

Tao,

Numbers allows us to specify just enough information to make the command unambiguous, and it takes it from there. There would be nothing wrong with specifying A10, but since only a single value is required for the criteria, if you specify the column, Numbers will infer the row part of the criteria address to be the same as the row in which the expression is written. If you wish to have the criteria to be taken from a different row, you can fully specify the cell address and that will override the normal inference.

Jerry

Tao,

unfortunately your problem statement is unclear. Can you post a screenshot and additional explaination? To post a screenshot use the procedure:

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