יום שלישי, 7 בפברואר 2023

Getting rid of all instances of duplicate values

Getting rid of all Instances of duplicate Values 

There are many methods in Excel to get rid of duplicate values: you have 2 instances of the same value and you want to keep only one and get rid of the second, superfluous value.

But what if you want to remove both instances of duplicate values?

So, here’s  trick I invented which allows you to accomplish this in one formula.

In column N we have a spilled array of dates, some of which are duplicate (marked in yellow: 13/04/2021, 15/04/2021, 19/04/2021).

The formula in cell P2 creates an array after removing the above mentioned duplicates. The trick here is to mark each duplicate (found with the COUNTIF function) with an intended “error” so that the TOCOL function will ignore it.

This post demonstrates a case where we have two duplicate instances but of course the same method can easily be applied where there are more than two such instances.

That’s all.



Pic 1: Remove all instances of duplicate values