Extract Unique Items From A List

This post will guide you how to extract unique itmes from a given list in Microsoft Excel. How to create a newly formula to get unique values from a range cells in Excel. The unique list of items is the first one found, which does not have a value equal to any other in the array. You can use one formula based on the INDEX function, the MATCH function in combination with the COUNTIF function to extract unique values. The generic formula is as follow:

=INDEX(range, MATCH(0, COUNTIF(unique using, range),0))

Explanation:


This formula will extract unique values from an array or range based on their position rather than just blank cells. First, you need to select the items for your unique list and place them in a range. Then you need to create a new formula in the next blank cell in your worksheet that states, “=INDEX(range, MATCH(0, COUNTIF(unique using, range),0))".

This formula will extract the unique values for just one column. If you prefer to have them in a different column, just drag the formula to that cell. You can also highlight the newly created formula and drag it down throughout your spreadsheet, but be sure to copy the values instead of cutting them before you do so since once you cut or delete them, they are gone.

Lets understand with an example:

"=INDEX($A$1:$A$8,MATCH(0,COUNTIF($C$1:C1,$A$1:$A$8),0))"

Extract Unique Items From A List In Excel1

The data in this table consists of the range cells $A$1:$A$8.

To create a formula that automatically sums up all of the values in an array, simply enter Control+Shift+Enter.

Clarification:


This formula starts with a basic index.

=INDEX(range,row)

This is telling Excel to find the row in your list of data where you want to start extracting unique items.

The hard work is figuring out which row number will give us unique values to identify which record needs more attention easily. This process starts with MATCH and COUNTIF; the main trick here is:

COUNTIF($C$1:C1,range)

With COUNTIF, we can see how many times an item already in our unique list appears on the master list. We use an expanding reference range ($C$1:C1) for this calculation, which means that it will return all values up until but not including $C$.

A growing reference is absolute on one side but relative when copied down. In this case, as the formula gets pasted into each new row of unique data in a list or table structure, it will continue expanding its reach until all available fields have been added with corresponding reference pointing.

To avoid creating a circular reference, we start one row C1 above, where the first unique entry is found. This means we want to count items *already* within that particular list and not just include the current cell. So instead, we begin on the highest able column available at the start.

All the headings should be unique, so they don’t show up in your master list too!

The criteria in COUNTIF returns an array when given multiple values. Therefore, each new row contains different results like this:

{0;0;0;0;0;0;0;0} // row 2

Extract Unique Items From A List In Excel1

{1;0;0;1;0;0;0;0}// row 3

{1;1;0;1;0;1;1;0}// row 4

As you can see, the MATCH function is able to identify which value in its array is zero. This means we can match each unique entry with a corresponding single-digit number and get all the desired data within our index formula.

The arrays we created in countif can be used to find positions (row numbers). For this, we use MATCH and set it up with an exact match. When put together our two sets produce a result like this:

MATCH(0,{ 0;0;0;0;0;0;0;0},0) // 1 (excel)

MATCH(0,{ 1;0;0;1;0;0;0;0},0) // 2 (word)

MATCH(0,{ 1;1;0;1;0;1;1;0},0) // 3 (access)

The MATCH locator counts items by looking for a count of zero (i.e., when there are no duplicates). This works because it always returns the first match, regardless if others have been found before yours.

INDEX is the key to finding what you’re looking for. It’s like a search engine that returns information by number rather than a name. With this simple difference in methodology comes huge benefits when quickly indexing large volumes of data.

Non-Array Version With LOOKUP


If you are using Excel 2021, you don’t have the INDEX function, but you do have LOOKUP. Lookup can accomplish most of what index can do, except it does it in a linear fashion. It is slow and requires you to enter one value at a time or copy/paste from an adjacent cell range to build your lookup table.

With this formula, you’ll be able to extract all unique items from any list in Excel:

=LOOKUP(2,1/(COUNTIF($C$1:C1, $A$1:$A$8)=0), $A$1:$A$8)

Extract Unique Items From A List In Excel1

The array operation can be done natively in LOOKUP by using a similar formula as INDEX MATCH.

  • Using this, you can simply fill in your list of unique items by using lookup’s maximum row parameter.
  • The COUNTIF function returns each value that occurs in the list. The result of COUNTIF is put in a Boolean array [0;1], which yields the count from each item.
  • Then divide each number by its own array, creating an even more precise error count.
  • When applied as an input to the LOOKUP function, this vector becomes the lookup table that stores all possible values for you!
  • The average value of 2 in this lookup_vector is larger than any other value.
  • LOOKUP always finds the last non-error value in an array.
  • When a value is found in LOOKUP, it returns that value. The range cells will contain all values returned by lookup when there is more than one occurrence of an input variable.”

This blog post discussed how to extract unique items from a list in Excel. We hope these tips will save you time and make it easy for you to get the most important data. If there are any other tricks or shortcuts we can provide, please let us know!

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel LOOKUP function
    The Excel LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
Related Posts

Break ties with helper COUNTIF and column

Suppose you got a task to adjust the values that contain the ties; what would be your first attempt to break the ties of the given value? If you are wondering about doing this task manually, let me add that ...

Find the Closest Data to the Data Provided in Excel

In our daily work, we may encounter such an issue that to find the closest value to a certain value. In fact, Excel internal functions can help us solve this problem. In today’s article, we will show you how to ...

Abbreviate Names Or Words in Excel
abbreviate names1

As an MS Excel user, you might have come across a task where you need to abbreviate different names or words, and there are also possibilities that you might have done this task manually by assuming that there isn't any ...

Convert State Names To Abbreviations
abbr state names1

Assume that you got a task to convert the full state's name into the abbreviations in MS Excel, and for doing this task, you might choose to do it manually, which is an acceptable way only if you don't have ...

Filter or Remove Columns
filter remove columns1

Assume that in MS Excel, you have a table consisting of a few columns consisting of few values, and you want to filter to remove the specified columns from the table. You might take it easy and would prefer to ...

Filter or Extract for Multiple OR Criteria
filter with multiple criteria1

If you are an valid MS Excel user, you have probably come across a situation where you wanted to filter the data in a separate table with specific criteria. You could do this task manually, which is also acceptable when ...

Filter Multiple Values
filter multiple value1

In Excel, you can easily filter a table to display only the rows that meet your criteria. This is a quick way to find the information you need without scrolling through all the data. In this post, we'll show you ...

Excel XLOOKUP Function

Excel XLOOKUP Function was added into Excel as a beta feature in August 2019 and is now accessible exclusively in Microsoft 365. (as of July 2021). However, if you fall into this category and often deal with big sets of ...

Extract or Filter Top n values
filter on top n values

You might have been through this kind of situation where you need to filter out the top n values from the list having few values, and I am also pretty sure about it that you might have chosen to do ...

Extract all the matches with helper Column
how to extract all matches with helper column1

With Excel's powerful functions IF, INDEX, and MATCH, we can find exactly what you're looking for with a few clicks of the mouse. This step-by-step tutorial will show how easy it is to extract data using these tools and more! ...

Sidebar