Need Help with Avoiding Duplicates in Numbers File

[raoullio]

Hi everyone,

I need some assistance with a .numbers file I'm working on. The file contains two sheets, "Sheet 1" and "Sheet 2". Both sheets have a column D that contains names. My goal is to check if the names in Column D of "Sheet 2" already exist in Column D of "Sheet 1". I want to add the rows from "Sheet 2" to "Sheet 1" without any duplicates in Column D.

I've been trying to find a solution for the past two weeks and have tried various formulas, but nothing has worked so far. If possible, I'd appreciate it if you could download the attached .numbers file, add the necessary formula, and ly re-upload it. Could someone please help me with the correct formula or method to achieve this? The attachment I have added is just a small example .numbers file with 6 rows, the real actual file has thousands of rows.

Important: please add formula only to Sheet 2 because Sheet 1 in my main Sheet and I do not want extra columns added in it. in the end I wanna see in Sheet 2 what the duplicates are.

Thank you so much for your help!
Ps: you can only upload .zip format, not .numbers format.

 

[iltwtv]

See the attached. Use the MATCH function to find where entries in Sheet 2 column D matches entries in Sheet 2 column D. Note the use of $ to used fixed references as shown on Sheet 2.

 

[NoBoMac]

Did not peek at the original or solution, but VLOOKUP is another option.

Or use of COUNTIF: I use that one to brute-force across multiple sheets in a particular workbook and then a conditional format to turn the cell red if count is 1 or greater. So something like this in column B in Sheet 2.

COUNTIF(Sheet 1::Table 1::$A2:$A7,$A2)

For Sheet 3:

COUNTIF(Sheet 1::Table 1::$A2:$A7,$A2)+COUNTIF(Sheet 2::Table 1::$A2:$A7,$A2)

 

[raoullio]

See the attached. Use the MATCH function to find where entries in Sheet 2 column D matches entries in Sheet 2 column D. Note the use of $ to used fixed references as shown on Sheet 2.

I have 1 question about your formula, is it possible to see a "TRUE" if it is a duplicate and "FALSE" if it isn't a duplicate.
What do I have to change in your formula? It works great by the way. Many thanks.

 

[iltwtv]

I have 1 question about your formula, is it possible to see a "TRUE" if it is a duplicate and "FALSE" if it isn't a duplicate.
What do I have to change in your formula? It works great by the way. Many thanks.

You’re welcome. Yes, it is possible. Change the cell E2 that I added with MATCH to:

IF(ISERROR(MATCH(D2,Sheet 1::Table 1::$D$2:$D$6,0)),"FALSE","TRUE")

Then autofill down the rest of column E. The $’s will keep the cells referenced in Sheet 1 fixed while the D2 will sequence to D3, D4, etc. to reference the corresponding cells in column D.

The ISERROR will return Boolean TRUE if MATCH returns an error (no match) and will return Boolean FALSE if MATCH returns the position of the match (a match). The IF statement returns “FALSE” if there is an error, that is, not a duplicate and returns “TRUE” if there is no error, that is, it is a duplicate.