How to Get Unique Items from a List in Excel Using Formulas

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.

Here is a combination of INDEX, MATCH and COUNTIF formulas that can get this done:

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)

=IFERROR(INDEX($A$2:$A$11,SMALL(MATCH($A$2:$A$11,$A$2:$A$11,0), SUM((COUNTIF($A$2:$A$11,$C$1:C1)))+1)),"")

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.

Add a Comment

Your email address will not be published. Required fields are marked *