I tried this formula, which works in excel, on numbers to eliminate duplicates, but in numbers it doesn't work.
I don't understand where I'm wrong.
The formula is in table 2 A2
=INDEX(TABLE1::$A$2:$A$20;MATCH(0;INDEX(COUNTIF($A$1:A1;TABLE 1::$A$2:$A$20););0))
[Re-Titled by Moderator]
iMac 24″, macOS 15.1
Numbers doesn't support "array" formulas like that. But there are MUCH easier ways than using a formula.
Try an 'Is Distinct' filter like this:
After the filter is turned on select the visible cells in the original table, command-c to copy, click once in a cell of an existing destination table, and command-v or Edit > Paste and Match Style to paste.
Or, just as easy, use a Pivot Table.
Click in the original table with the duplicates, choose Organize > Create Pivot Table > On Current Sheet and drag the relevant field into the Rows box as shown here. Then copy-paste from the Pivot Table into your destination table.
More on Filters here:
Filter data in Numbers on Mac - Apple Support
More on Pivot Tables here:
Intro to pivot tables in Numbers on Mac - Apple Support
SG
Numbers doesn't support "array" formulas like that. But there are MUCH easier ways than using a formula.
Try an 'Is Distinct' filter like this:
After the filter is turned on select the visible cells in the original table, command-c to copy, click once in a cell of an existing destination table, and command-v or Edit > Paste and Match Style to paste.
Or, just as easy, use a Pivot Table.
Click in the original table with the duplicates, choose Organize > Create Pivot Table > On Current Sheet and drag the relevant field into the Rows box as shown here. Then copy-paste from the Pivot Table into your destination table.
More on Filters here:
Filter data in Numbers on Mac - Apple Support
More on Pivot Tables here:
Intro to pivot tables in Numbers on Mac - Apple Support
SG
If you want/need to use a formula, here is a formula that will flag duplicates in a column of data. It is more complicated than what we used to do with COUNTIFS but Numbers has a long-standing bug that screws up the COUNTIF method if you try to sort the table.
C2 =IF(ROW()−1=XMATCH(B2,B,0,1),B2,"duplicate")
Fill down
The -1 is to account for the one header row in the table.
Here's how I solved the problem to have a list without duplicates
formula in B2
formula in C2
new table without duplicate
formula in list A2
The formulas are not my invention, but an adaptation of others recommended in this forum
As always you're right.
The two ways you recommended are much simpler, although I hope that Apple dedites more time to applications as it does with the settings. Thank you
Thanks for the formula, but I would need the possibility to create the list excluding duplicates as the pivot table does, but unlike the pivot table it does it automatically.
Duplicate elimination formula not working in Numbers
