Filter unique distinct and duplicate values from a large data set in excel 2007
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
- Click black arrow
- Click "Sort A to Z"
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:
Filter duplicates
- Click black arrow on column 2
- Disable blanks
- Click OK
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
- Click black arrow on column 2
- Disable blanks
- Click OK
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
Related posts:
Excel 2007/2010 array formula: Filter unique distinct values, sorted and blanks removed
Filter unique distinct values from two ranges combined in excel 2007
Excel udf: Filter unique distinct values (case sensitive)
Excel udf: Filter unique distinct records (case sensitive)
Count unique distinct values in a large dataset with a date criterion
Recommended categories
I'm wanting to import the larger of two cells containing dates from one workbook into a cell in a separate workbook. What would be the quickest way to do that?
Thanks,
Vince
Vince,
I don´t know the quickest function but I tested MAX(value1, value2, ..) on 1 million dates and it is pretty quick. Max function accomplishes the task in a couple of seconds.