Same data but ignoring duplicate

[sneakyla]

Hello!

I'm trying to find the formula to copy the same data from another sheet but ignore all duplicates. It does not have to be in order. I've been using just a simple "=A2" to have a cell replicate a different cell but it's also replicating duplicates and that's what I'm trying to avoid.

For example

Sheet 1 (main sheet with formula)
001
002
003
004
005
006
007

Sheet 2 (data sheet)
001
002
003
004
001
002
003
006
007
007
002
005

Thank you!

 

[jasonb75]

Are you still using excel 2016 as shown on your profile?

If you have recently upgraded to either office 365 or excel 2021 then you will have the UNIQUE function which is perfect for the task.

Another simple option is to copy and paste the list instead of using a formula, then use the duplicate removal tool (keyboard shortcut sequence Alt a m).

 

[sneakyla]

Thank you for the response. The unique formula wasn't working properly because it was reading a formula data so it kept giving me #SPILL error.

However, I found the solution to this with another formula. I'm using the formula below and it solved it! I added IFERROR for a longer list of data that goes down with no info so it doesn't show error message and instead shows a blank cell.

=IFERROR(INDEX($A$1:$A:$500, MATCH(0, COUNTIF($B$1:B1,$A$1:$A:$500), 0)),"")

 

[jasonb75]

The unique formula wasn't working properly because it was reading a formula data so it kept giving me #SPILL error.

The UNIQUE function can read other formulas without resulting in #SPILL errors when used correctly. Remember that it only needs to be entered into 1 cell and the rest will be done for you. If you have tried to drag it down then you will see the error.

=UNIQUE(FILTER(A1:A500,A1:A500<>""))

 

[sneakyla]

The UNIQUE function can read other formulas without resulting in #SPILL errors when used correctly. Remember that it only needs to be entered into 1 cell and the rest will be done for you. If you have tried to drag it down then you will see the error.

=UNIQUE(FILTER(A1:A500,A1:A500<>""))

Ohhhh got it! Thank you so much for this!