How to display only duplicate values in an Excel column?
- Create a test with the COUNTIF function
- The COUNTIF function allows you to count the lines corresponding to a criterion.
- We will use this function to create a test when we find 2 or more data
- Use the FILTER function in Excel 365 to keep values present more than once
In this example, the data has been added to a table whose name is tbl_Data.
Build the test to find duplicate values in Excel
Since the arrival of propagated functions, the entire construction of the formulas has been transformed in Excel. Thus, we can write the COUNTIF function with a range of data as a search criterion.
=NB.IF(tbl_Data[Value];tbl_Data[Value])
Then, just add a logic test to know if each value is present more than once
=NB.IF(tbl_Data[Value];tbl_Data[Value])>1
Return duplicate values only
La FILTER function expects to receive a logical test as a filtering criterion. We will indicate the result of the test with the COUNTIF function as a criterion for the FILTER function.
=FILTER(tbl_Data[Value];COUNTIF(tbl_Data[Value];tbl_Data[Value])>1)
Keep each value only once
To finish, all we have to do is integrate this formula into the UNIQUE function to keep only one value
=SINGLE(FILTER(tbl_Data[Value];COUNTIF(tbl_Data[Value];tbl_Data[Value])>1))
Related Articles
It is this same technique that allowed us to find the non-matches between 2 columns.
