51 EXCEL TIPS EBOOK
22,000+ Excel enthusiasts have already downloaded
In this blog post, I will show you a formula to get a list unique items from a list in excel that has repetitions. While this can be done using Advanced Filter or Conditional Formatting, the benefit of using a formula is that it makes your unique list dynamic. This means that you continue to get a unique list even when you add more data to the original list.
Get Unique Items from a List in Excel Using Formulas
Suppose you have a list as shown above (which has repetitions) and you want to get unique items as shown on the right.
How it works
When there are no more unique items, the formula displays an error. To handle it, I have used the Excel IFERROR function to replace the error message with a blank.
Since this is an array formula, use Control + Shift + Enter instead of Enter.
This is a smart way to exploit the fact that MATCH() will always return the first matching value from a range of values. For example, in this case, MATCH returns the position of the first 0, which represents the first non-matching item.
I also came up with another formula that can do the same thing (its longer but uses a smart MATCH formula trick)
I will leave it for you to decode. This is again an array formula, so use Control + Shift + Enter instead of Enter.
In case you come up with a better formula or a smart trick, do share it with me.