Extracting without duplicates is a very common operation in Excel. This article will present two solutions to obtain a list without duplicates from formula.
Extract without duplicates with formula
In Excel you can extract a list of data without duplicates using the tool Data > Remove duplicates
However, if your initial list of data is updated regularly, you must redo this work again and again. To avoid this, there are two formulas:
- La nouvelle UNIQUE function present in Microsoft 365, Excel 2019 and Excel Online.
- A very complex array formula for other versions of Excel.
UNIQUE function
If you are working with the Microsoft 365 version or the free Excel Online version, you have the UNIQUE function. This function, very simple to write, allows you to dynamically extract a list without duplicates.
UNIQUE also allows you toextract present values only once in a list of values.
Formula to extract without duplicates (old method)
Now, if you don't have Microsoft 365, you don't have the dynamic matrix functions ?. However, you can extract a list of data using a formula but the function is complex.
The solution here is the work of the undisputed master of matrix formulas: Mike "ExcelIsFun" Girvin. The formula for extracting all unique values from a list is:
=INDEX(Ma_Colonne;PETITE.VALEUR(SI(FREQUENCE(SI(Ma_Colonne<>"";EQUIV(Ma_Colonne;Ma_Colonne;0)); LIGNE(Ma_Colonne)-LIGNE($B$2)+1);LIGNE(Ma_Colonne)-LIGNE($B$2)+1);LIGNES(I$2:I2)))
To validate an array formula, you must simultaneously press the keys Ctrl + Shift + Enter
Explanation of function parameters
- My_Column by the cell range that contains the data you want to extract.
- B2 by the first cell of your column where there are the duplicates.
- I2 by the cell in which you want to see the result (normally writing the reference of the result cell in the calculation formula of this same formula results in a circular reference but not in the case of an array formula).
The whole heart of the formula is the part SMALL.VALUE.
This part retrieves the position of the next value different from the previous ones. And since this formula is included in the INDEX function, we bring back all the unique values of our column.
Related Articles
You will find additional information on filter unique values or remove duplicates on the Microsoft site.
07/07/2022 @ 12:39 p.m.
Hello,
Nice tutorial,
The matrix function does not work in my example.
In particular LINES(C$25;C25) only returns 1s, and as a result I only have the first unique, non-empty one; the rest of the function functions roughly like a COUNTIFS matrix.
After a lot of research to understand how the formula works, I corrected it with:
=INDEX(Ma_Colonne;PETITE.VALEUR(SI(FREQUENCE(SI(Ma_Colonne"";EQUIV(Ma_Colonne;Ma_Colonne;0));LIGNE(Ma_Colonne)-LIGNE($D$25)+1);LIGNE(Ma_Colonne)-LIGNE($D$25)+1);LIGNE(Ma_Colonne)-LIGNE($D25)+1))
07/07/2022 @ 10:59 p.m.
Hello,
Very nice tutorial.
In the absence of Excel 365, I tried the matrix formula.
=INDEX(Ma_Colonne;PETITE.VALEUR(SI(FREQUENCE(SI(Ma_Colonne"";EQUIV(Ma_Colonne;Ma_Colonne;0)); LIGNE(Ma_Colonne)-LIGNE($D$10)+1);LIGNE(Ma_Colonne)-LIGNE($D$10)+1);LIGNES(C$10:C10)))
C10 is the start of the matrix.
D10 the start of the column to be tested.
My_Column defines as $D$10:$D$25
The result is surprising. C10:C25 takes as value the 1st non-empty value present in D10:D25, without going to the next one.
(for other users, the lack of resources message occurs when you indicate a complete column instead of a range on My_Column)
16/04/2022 @ 17:41 p.m.
Hello
I'm looking to use the small.value and large.value functions as conditional formatting in a TCD with numbers to highlight the largest number, then the 2nd largest number, and finally the smallest number...
The problem is the duplicates... and as the table changes often I cannot set up ranks...
Do you have a solution please?
In advance, thank you 🙂
13/12/2021 @ 15:42 p.m.
Hello,
Thank you for your article, it is very interesting.
However, I wanted to know if there is a possibility of finding a value based on a single criterion. Basically grabbing the same information without having a duplicate.
In my example I have orders that will leave at the same time, and I want to search for the orders according to the time without Excel repeating the order for me.
Time Commands
At 13h
B 13 p.m.
C 13 p.m.
D 13 p.m.
Thank you in advance,
Sincerely,
13/12/2021 @ 16:32 p.m.
Use the FILTER function
14/12/2021 @ 09:21 p.m.
Thank you, it works perfectly!
17/11/2021 @ 10:31 p.m.
Hello,
I have a list of different products that appear multiple times and I would like to be able to get the number of different products that are listed.
For example:
- apple
- pear
- apple
- apple
- pineapple
What is the formula that allows me to know how many fruit references there are (in this case 3)?
Thanks in advance !
Caroline
17/11/2021 @ 11:15 p.m.
La COUNTIF function (quite simply)
17/11/2021 @ 12:44 p.m.
A discovery for me! Thank you very much, I'll try this right away!
17/11/2021 @ 13:04 p.m.
I think there is one thing I'm missing, what I'm looking for is to make the name of different fruits appear (3 = apple / pineapple / pear) regardless of the number of times they appear (apples = 3, pear = 1; pineapple = 1).
Is such a thing possible?
20/11/2021 @ 14:25 p.m.
It's possible but not with a function. By macro or Power Query it is possible
12/08/2022 @ 13:54 p.m.
You must use the UNIQUE function ("array";False;False)
24/08/2021 @ 16:29 p.m.
Hello,
Is it possible to have the results sorted alphabetically (or at least in ascending order) with the matrix method?
Thank you in advance
24/08/2021 @ 17:11 p.m.
It is necessary to add the SORT function
25/08/2021 @ 08:02 p.m.
After searching your site, I thought about it, but I don't have this function on my version of Excel... (Office Pro Plus 2016)
I managed to get around the problem by going through a set of already sorted data, but I would still be interested (for the future) to know if this is possible without the functions provided by Office 365.
Thanks again.
25/08/2021 @ 10:26 p.m.
All developments are done for Microsoft 365. Without wanting to change your current versions, the easiest way would be to do a version migration. Otherwise, with Excel Online you have all the latest functions
03/05/2021 @ 18:14 p.m.
Hello,
Do you have a solution to find unique values, in cells, but for a table with multiple rows and multiple columns?
Thank you
03/05/2021 @ 19:07 p.m.
Yes ! With this article you will find the answer. You like ? Share 😉
20/03/2021 @ 10:30 p.m.
Hello,
What formula to find, how many times a number or digit (duplicates) is used in a numerical table in Excel?
Thank you in advance for your answer
20/03/2021 @ 11:01 p.m.
Quite simply with the COUNTIF function
10/09/2020 @ 17:46 p.m.
Hi,
I have an excel file containing two customer data sheets. I would like to know if the data from my sheet 1 is found in my sheet 2. Knowing that the data entered is not always the same.
Ex.: sheet 1 -> café, restaurant Le Bijou, Pamplona
sheet 2 -> Bijou restaurant, Pamplona North
So what can you do to avoid having to do a manual search line by line. With the search function v and estan and if, I can't do it.....A little help......
10/09/2020 @ 18:20 p.m.
Clean data is the key to success in Excel.
You should start by "cleaning" the cells using the instant filling or use the tool close search by Power Query
12/10/2018 @ 08:41 p.m.
Thank you very much for this matrix formula. Your instructions are great. I love it and yet I don't know how to do this type of function. THANKS
07/03/2018 @ 21:36 p.m.
Hello,
Is this function only useful for characters or even numbers.
pi: I tested this function but unfortunately no success
Thank you
14/02/2018 @ 22:44 p.m.
Hello,
I applied the formula as explained but without success. Excel gives me an alert: Excel ran out of resources when trying to calculate one or more formulas. These formulas cannot be easily evaluated.
I work with Excel version 15.32 on Mac
Being the last formula I added, it can only be this one.
Do you have an explanation ?
thank you in advance
30/06/2021 @ 15:08 p.m.
Hello,
I also tried the formula but it didn't work and I got the same message. I don't know if there are manipulations that I missed or things like that.
30/06/2021 @ 15:53 p.m.
Heeeuuuu, it's impossible to give you an answer without seeing the context (in short, the construction of the workbook)
But the UNIQUE formula does not work on your version?
04/03/2016 @ 17:52 p.m.
Hello, please is there a function that allows us to view duplicates and transfer them to a new sheet
Thank you