Extracting Values Appearing More than Twice

Nolan has a column of unsorted values, comprising more than 2,500 rows. He would like to extract a list of values that appear more than twice in the column. He wonders if there is a formulaic way to do this.

A formulaic approach requires the use of the newer dynamic array functions. Assuming that the values are in column A, you can use this formula:

=UNIQUE(FILTER(A1:A3000, COUNTIF(A1:A3000, A1:A3000) >2))

The FILTER function filters the data in column A according to the specification that there are more than two of the value, and then UNIQUE returns the unique values from the results of the FILTER. You can also use the LET function to approach the task from two different angles:

=LET(X,A1:A3000, Y,COUNTIF(X,X), UNIQUE(FILTER(X,Y>2)))
=LET(X,A1:A3000, Y,UNIQUE(X), FILTER(Y,COUNTIF(X,Y)>2,""))

If you are using an older version of Excel (all the formulas so far require Excel 2021, 2024, or Microsoft 365), then there is not a simple formulaic solution. Instead, you'll need to use a helper column to specify the number of instances of each value and then use the Advanced Filter tool to copy those values with greater than 2 instances to another location. Even though Nolan specifically asked for a formulaic solution, here's how you would implement the Advanced Filter approach:

1. Make sure the data in column A has a column heading in cell A1, such as Values.
2. In cell B1, enter a heading such as Count.
3. In cell B2 enter the following formula:

=COUNTIF(A$2:A$3000,A2)

4. Copy the formula down as many cells as necessary.
5. In cell D1 enter the same heading you placed in cell B1 (Count).
6. In cell D2 enter >2.
7. Select any cell in column A.
8. Display the Data tab of the ribbon.
9. Click the Advanced tool. Excel displays the Advanced Filter dialog box.
10. Make sure the Copy to Another Location radio button is selected.
11. Make sure the List Range includes all the cells in columns A and B.
12. In the Criteria Range box, enter the range $D$1:$D$2.
13. In the Copy To box, specify a cell where you want the filtered results copied.
14. Make sure the Unique Records Only check box is selected.
15. Click on OK.

When you see how many steps it takes to perform such a filtering operation, you can easily understand why users were excited for the newer dynamic array worksheet functions that made a formulaic extraction possible.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3196) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.