how do i find a duplicate row In numbers

With that many rows I find it much easier to just use a short script rather than struggle with formulas in extra columns and then have to clean up afterwards.

No need to know AppleScript. Just:

1. copy-paste into Script Editor (in Applications > Utilities)
2. click somewhere in the table with the data, and click the triangle 'run' button in Script Editor.

(The first time make sure Script Editor.app is checked at System Preferences > Security & Privacy > Privacy > Accessibility)

Duplicate rows will be marked in red, something like this.

SG

tell application "Numbers"

tell front document's active sheet

tell (first table whose selection range's class is range)

set rowsRead to {}

repeat with r in rows

set vv to r's cells's value as text

if vv is in rowsRead then

set r's background color to "red"

else

copy vv to rowsRead's end

end if

end repeat

end tell

end tell

end tell

add a new column, E, and in that column place the formula:

F1=A1&B1&C1D1&E1

this is shorthand for... select cell F1, then type (or copy and paste from here) the formula

=A1&B1&C1D1&E1

G1=countif(F, F1)

now fill these formulas down by doing the following:

- select cells F1 and G1, copy

select cells F1 thru the end of column G, paste

in column G any number larger than 1 has a duplicate. then you can sort by column G descending to see all the duplicates

Hi Jaffa,

Does the order of the numbers count?

In the example, column H indicates only two rows, "A" and "D" are duplicates,

while column J indicates that every row except "B" contains the same numbers as one other row.

Both H and J use COUNTIF to check for duplicates in the 'key' column to their immediate left:

H2: COUNTIF(G,G2) J2: COUNTIF(I,I2)

Fill down to last row in table.

The Key formula in column G concatenates the contents of the cells in its row of columns B to F, into a text string including all the digits in the order they appear in the columns.

G2: B&C&D&E&F

The Key formula in column I reads all the numbers in its row of columns B to F, then returns them in order from smallest to least small, concatenating those results into a single text string of the numbers in ascending order.

I2:

IF(COUNT(B2:F2)<5,"",SMALL(B2:F2,1)&SMALL(B2:F2,2)&SMALL(B2:F2,3)&SMALL(B2:F2,4)&SMALL(B2:F2,5))

The sort and concatenation are done by the part of the formula in bold. The rest is a switch that prevents the calculation from happening until there are five entries in the five cells in columns B to F on that row.

Regards,

Barry