Author: Oscar Cronquist Article last updated on December 17, 2018

Question:

hi all,

thanks for the great formula/array formula. it works great.

lately, i noticed that the array formula will make the excel calculations CRAWL if I have thousands of entries (dates; but mostly repeated because of different product).

e.g.

1-Jan-2010 ProductA
1-Jan-2010 ProductB
1-Jan-2010 ProductC
1-Jan-2010 ProductD
2-Jan-2010 ProductA
2-Jan-2010 ProductB
2-Jan-2010 ProductC
...
...

the unique distinct values from the dates are 1-Jan-2010, 2-Jan-2010, and so on.

In a month (31-days) x 13 products = 403 entries. Multiply this to 12-months = 403 x 12 months ~est. 4.9k dates to be parsed.

the jackpot question: Is there a way to easily parse long list, without sacrificing performance (recalculations)?

thx!

A comment from this blog post: How to extract a unique distinct list of a column in excel

Answer:

I created a random list of 5000 values. Creating a unique distinct list using Advanced Filter only took a couple of seconds. Maybe that is fast enough?

I also tried sorting the list and using MATCH() (which is fast) to filter unique distinct values but that required a lot of manual work. Maybe someone else have some lightning fast formula technique?

I then created a macro, recording my actions using Advanced Filter. I edited the vba code and inserted a button (Form Control).

How to Run an Excel Macro With a Worksheet Button

Clicking the button refreshes the unique distinct list. Adding values to the list is no problem, the selection extends as long as there are no blank cells.

### How to automatically use Advanced Filter to create a unique distinct list

1. Click "Developer" tab How to show the Developer tab or run in developer mode
2. Click "Visual Basic"
3. Create a "Module" for your workbook How to Copy Excel VBA Code to a Regular Module
4. Copy this vba code into module:

```Sub AdvFilter() ' ' AdvFilter Macro
' Select first cell in column (Sheet1!A2)
Range("Sheet1!A2").Select
' Extending the selection down to the cell just above the first blank cell in this column
Range(Selection, Selection.End(xlDown)).Select
' Execute Advanced Filter on selection and copy to Sheet1!C2
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Sheet1!C2"), Unique:=True
End Sub
```

### Assign macro to button

How to Run an Excel Macro With a Worksheet Button

### Download excel sample file for this tutorial.

Remember to enable macros.

quick unique distinct list.xls
(Excel 97-2003 Workbook *.xls)