Ignore Duplicates and Create New List of Unique Values in Excel

by

Totero’s answer is correct. The link is also very helpful.

Basically the formula you need is:

B2=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

Then press ctrl+shift+enter (or it will not work using a array formula).

Two important things to keep in mind here: The complete list is in cells A2:A20, then this formula has to be pasted in cell B2 (Not B1 as that will give you circular reference).
Secondly this is an array formula, so you need to press ctrl+shift+enter or it will not work correctly.

More Related Contents:

  1. Remove leading or trailing spaces in an entire column of data
  2. Excel: Searching for multiple terms in a cell
  3. Optimizing Excel formulas – SUMPRODUCT vs SUMIFS/COUNTIFS
  4. How to convert to percentage [closed]
  5. Sequence of numbers based on start and end value of 2 cells [closed]
  6. I have an Excel data sheet that I would like to extract data from into a new sheet
  7. Error in finding last used cell in Excel with VBA
  8. Getting unique values in Excel by using formulas only
  9. Vlookup using 2 columns to reference another
  10. Excel: last character/string match in a string
  11. Referencing value in a closed Excel workbook using INDIRECT?
  12. filter out multiple criteria using excel vba
  13. Simple Pivot Table to Count Unique Values
  14. Excel UNIQUE Across Columns
  15. Filling any empty cells with the value above
  16. How to create a string or formula containing double quotes in Excel?
  17. Find last used cell in Excel VBA
  18. Two column lookup in table array using INDEX and MATCH
  19. Excel Date to String conversion
  20. Excel: Formulas for converting data among column / row / matrix
  21. Three Dimensional Lookup Using INDEX/MATCH
  22. Cannot VBA write data to cells in Excel 2007/2010 within a function
  23. Countif With Multiple OR Criteria
  24. Dynamic chart range using INDIRECT: That function is not valid (despite range highlighted)
  25. How to split texts from dynamic range?
  26. Excel: the Incredible Shrinking and Expanding Controls [closed]
  27. Excel: Search for a list of strings within a particular string using array formulas?
  28. Convert from US to UK format
  29. VBA Excel – IFERROR & VLOOKUP error
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment