Often using less feature bloated products is a real win, but sometimes it's a real pain.
While users of Microsoft Office can use a handy tick box to exclude duplicate rows, users of Apple's Numbers are less fortunate. But there is a relatively simple custom function you can write that will accomplish this and give you greater flexibility.
(An example spreadsheet is attached below.)
This is a two-step process
First: Create a new column of cells that you'll aggregate the text from the columns you want to compare (this is handy as we can just compare the columns we want).
Sample: = A25 & B25 & C25 & D25
Second: Create another column that will contain the text for flagging, in my sample I used the word "duplicate" and added some syntax highlighting to make it pop.
Sample: = IF (( MATCH ( H25,$H$2:$H$520 ) + 1 ) <> ROW ( H25 ) ,"duplicate","")
That is, if the text appears more than once in the aggregated value column more than twice, set the text of the of this field to "duplicate."
Bonus: If you want to purge the duplicate values, sort the column with your matched text and delete the rows.
An example spreadsheet is attached below.
- Apple Number sample spreadsheet
- Microsoft Excel (UNTESTED) conversion
