Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

How to create a conditional formatting to find duplicates?

How to create a conditional formatting to find duplicates on Numbers Mac/iOs/Web

Just that... Can u tell me how? It should be quite simple. (I hope)



MacBook Pro 13", macOS 10.15

Posted on Apr 6, 2020 8:15 AM

Reply
Question marked as Apple recommended

Hi Pilo,


Conditional highlighting rules compare the value in a cell with either a fixed value, written into the rule, or with the value in another cell. A cell-to-cell comparison would require comparing each cell in the document to every other cell, so a direct comparison is out.


COUNTIF counts the number of cells meeting a stated condition, offering a means of providing highlighting to a cell in a row containing an entry duplicating the entry in another row.


Add a column to your table, adjacent to the one containing the entries to be compared. For the examples, entries to be compared are in column B, the added column is column C.

Column C contains the first version of the formula shown below the table. It counts the number of times the value in 'this row' of column B appears in all of column B. Any value greater than 1indicates 'this value' is duplicated'.

Column D contains the second version of the formula shown below the table, which counts an expanding range of cells from the beginning of the table to the 'row above this row' to produce the number of times 'this entry' has already appeared in the column' Any number greater than 0 indicates the value in 'this row' is a duplicate of a value in the cells above this one.


Enter either formula in row 2 of the column to be highlighted, then fill down to the end of that column.


Both column show use the same CH rule, differing only in the number placed in the box.


CH rule for column C:


The CH rule for column D is the same, with "1" replaced with "0".


Regards,

Barry




Posted on Apr 6, 2020 10:53 AM

1 reply
Question marked as Helpful

Apr 6, 2020 12:10 PM in response to Barry

Hi Barry. First of all, thank you for your answer. You have been very detailed. I understand it can be done this way, but as an Excel user where it can be done with just a few clicks, its seems as an horrible experience (not apple at all). Add two columns for each one where you just want to find duplicates it's hideous.


From time to time I test Numbers and it's capabilities to see if it could be used as an excel alternative, at least for the tools I commonly use.


Nowadays it seems that Numbers its still a "toy" as a spreed sheet. I will keep with the old Excel for now and give it a try in one more year.


Have a nice day.

3 replies
Question marked as Apple recommended

Apr 6, 2020 10:53 AM in response to Pilo7

Hi Pilo,


Conditional highlighting rules compare the value in a cell with either a fixed value, written into the rule, or with the value in another cell. A cell-to-cell comparison would require comparing each cell in the document to every other cell, so a direct comparison is out.


COUNTIF counts the number of cells meeting a stated condition, offering a means of providing highlighting to a cell in a row containing an entry duplicating the entry in another row.


Add a column to your table, adjacent to the one containing the entries to be compared. For the examples, entries to be compared are in column B, the added column is column C.

Column C contains the first version of the formula shown below the table. It counts the number of times the value in 'this row' of column B appears in all of column B. Any value greater than 1indicates 'this value' is duplicated'.

Column D contains the second version of the formula shown below the table, which counts an expanding range of cells from the beginning of the table to the 'row above this row' to produce the number of times 'this entry' has already appeared in the column' Any number greater than 0 indicates the value in 'this row' is a duplicate of a value in the cells above this one.


Enter either formula in row 2 of the column to be highlighted, then fill down to the end of that column.


Both column show use the same CH rule, differing only in the number placed in the box.


CH rule for column C:


The CH rule for column D is the same, with "1" replaced with "0".


Regards,

Barry




Question marked as Helpful

Apr 6, 2020 12:10 PM in response to Barry

Hi Barry. First of all, thank you for your answer. You have been very detailed. I understand it can be done this way, but as an Excel user where it can be done with just a few clicks, its seems as an horrible experience (not apple at all). Add two columns for each one where you just want to find duplicates it's hideous.


From time to time I test Numbers and it's capabilities to see if it could be used as an excel alternative, at least for the tools I commonly use.


Nowadays it seems that Numbers its still a "toy" as a spreed sheet. I will keep with the old Excel for now and give it a try in one more year.


Have a nice day.

Apr 6, 2020 5:17 PM in response to Pilo7

Hi Pilo,


Overly detailed, it would appear. :-)



"Add two columns for each one where you just want to find duplicates it's hideous."


The only reason for two columns in my example was to demonstrate two different results. Either column could be omitted without affecting the other.


Column C marks ALL copies of each duplicated item.


Column D marks only the second, third, fourth… copy.


"I will keep with the old Excel for now and give it a try in one more year."


I suspect you'll keep using MS Excel for far longer than another year. It's been pretty obvious from the beginning that Numbers is not and isn't intended to be an Excel clone.


The general recommendation here has been to use the tool that fits the job. For some of the people here, that mean using both Excel and Numbers, choosing each according to which would be the better tool for the situation.


Regards,

Barry


How to create a conditional formatting to find duplicates?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.