In this post I am going to describe how to filter duplicate and unique distinct values from a really large dataset. Both array formula and advanced filter are too slow.
In this example, cell range A1:A1 000 000 contains seven random letters from A to Z.
Create a table
- Select cell A1
- Press Ctrl + A . This selects cell range A1:A1000000.
- Click "Insert" on the ribbon
- Click "Table" button
- Click OK.
Sort values in column A
Sorting takes 15-20 seconds on my old computer.
Excel Formula - Find duplicates
- Select cell B3
- Type=IF(B3=B2,B3, "")
- Press Enter
Formula calculation for the entire range takes maybe 15 to 20 seconds. To make it case sensitive, change formula to:
How to return unique distinct values
Make sure the range is sorted.
Change formula in cell B3:
To make the formula case sensitive, change formula to:
Filter unique distinct values
Download excel file
Filter unique distinct values from a large data set.xlsx
Excel 2007 *.xlsx
Recommended blog posts
Want to learn more about filtering unique distinct values? You must read these blog posts:
- Extract a unique distinct list from two columns using excel 2007 array formula
- Extract a unique distinct list from three columns in excel
- Unique values from multiple columns using array formulas
- Unique list to be created from a column where an adjacent column has text cell values
- Create unique list from column where an adjacent column meets criteria
- How to create a unique distinct list where other columns meet two criteria
Learn more about sorting unique distinct values. Read these blog posts:
- Extract a unique distinct list sorted from A-Z from range in excel
- Sort a range by occurence using array formula in excel
- Create a unique list and sort by occurrances from large to small
Learn more about counting unique distinct values and records. Read these blog posts:
Learn more about filtering and comparing unique distinct records. Read these blog posts:
- Filter unique distinct records in excel 2007
- Compare two lists of data: Highlight common records in excel
- Compare two lists of data: Filter records existing in only one list in excel
- Compare two lists of data: Filter common row records in excel
- Quickly compare two tables in excel 2007
Read more about custom functions in excel