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.
.png)
