How To 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:

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.

The Ultimate Guide to Find and Remove Duplicates in Excel.

{=IFERROR(INDEX([Orginal List],MATCH(0,COUNTIF(OFFSET(Unique[[#Headers],[Unique Value]],ROW()-1-ROW(Unique[[#Headers],[Unique Value]]),0,ROW(Unique[[#Headers],[Unique Value]])-ROW()),[Orginal List]),0)),””)} Where Unique is the Table name and Original List ,Unique Value are its column. Also, can you create a helper (additional) column, copy paste this data to get it in one column and use this formula, or you looking for a dynamic formula? =IFERROR(IF(ROWS($C$2:C2)<=COUNT($A$2:$B$18),INDEX($A$2:$B$18,IF(ROWS($C$2:C2)<=COUNT($A$2:$A$18),ROWS($C$2:C2),ROWS($C$2:C2)-COUNT($A$2:$A$18)),IF(ROWS($C$2:C2)<=COUNT($A$2:$A$18),1,2)),""),"") This would give you a single column list with data from both the columns (and this is dynamic) I am sure there could be a shorter way, but if this works for you, nothing like that. =IFERROR(IF(ROWS($C$2:C2)<=COUNT($A$2:$A$18,$E$2:$E$18),INDEX($A$2:$E$18,IF(ROWS($C$2:C2)<=COUNT($A$2:$A$18),ROWS($C$2:C2),ROWS($C$2:C2)-COUNT($A$2:$A$18)),IF(ROWS($C$2:C2)<=COUNT($A$2:$A$18),1,5)),""),"")